#VALUE / Pulls Stock Prices Every Minute And Update Page
Apr 18, 2006
I have a script that pulls my stock prices every minute and update an excel page I've created...basically an excel stock portfolio...the problem I'm running into is when a stock is unchanged...instead of being $0.00 it returns "unch"...as you can imagine this throws my calculations into chaos...I have the following formula but it doesn't seem to be working =IF(J18<0,R18*J18,(IF(J18>0,R18*J18,0)))
Basically J18 is the returned change in stock price...R18 is the number of shares held...how can I get it to view "unch" as $0.00?
i am using excel 2007 on windows xp. here is what i have, i have 5 scanners that i want to track to see how much they scan per minute in a day, week, and month period. here are my data in excel for one scanner in a 8 hour shift:
500047022059574
A1 = the image they scan for a day B1 = hours they worked that day (cell formatted to [m]) C1 = A1/B1
I have to update our ticker(US and Can stock) prices manually in our portfolio system.
I use www.bigcharts.com as our source . I can import the file in Excel. So what I would like to do is update the prices in Excel from the bigcharts.com. Not just today's prices but prices from previous day or two as well. Once I go to the website, I click historical quotes, then type in the stock symbol, and the date ,then write down the price.
Would Excel web query be the best way to approach this? If so, how would I import the the price for that particualr ticker for that date from the website into my excel?
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.
At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.
However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.
Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.
So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.
I am trying to grab the value of a cell every time it updates and copy that value and store in a new cell.
So I have a cell (A2) and it updates every 60 seconds, I need that value for t=1 (first time) to be copy and pasted into a new cell (B2). Then when t=2 (second time, 60 seconds later) I need the value to be pasted into the same column but a new row, namely cell C2. Then when t=3 (third time), I need it to be copied to cell D2 and so on.
I'm assuming it just a macro that is called every 60 seconds and grabs the value of A2, but then needs to find the next cell in column B that is empty and paste it there.
I have a price list from my supplier with the new prices.
I then have my Accounting software where I need to update the cost and retail prices. The problem I have is the Accounting software has allocated it`s own Unique ID for each item. So in order for me to bulk import this I need to keep this unique ID with the Actual Product ID together otherwise it will duplicate the product.
Here is a example
Sheet to be updated (Cost and Retail only) from PRICE LIST SHEET Uneque ID SKU
I have attached a sample of our worksheet (GTS807) used to create quotes and generate jobsheets etc once they are orders. To ensure we have enough of a size in stock I want to carry out a check against the stocksheet (stock) which I have no problem but I cant get it to automatically update the stock once a quote becomes an order and removes the quantity from stock!
As you see the balance of stock in shown in AA13 for "115270" but is there a code to find "115270" on the stock sheet and copy the new balance back there?
create a Macro that runs when system clock changes minute and then at every change of minute of system clock.
For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...
I already have a Macro that runs every minute from initial run time, using
Code: Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"
but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.
I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
I did try a search before posting this - either I am too stuipid or the info was a bit too cpmplicated and in bits and peices for me to figure out. My query is this Lets say I have a workbook with around 10 sheets in it. and I have a saved a copy of the sheet ias a webpage (HTML ) for user convinence. Now is it possible to somehow ensure that when the orginal workbook is updated then this webpage would also get updated - both would be in the same drive ( I dont want to use internet for this purpose)
How can I take the main data sheet and have the information transfer to another sheet? It will be transferring to a "condensed" version with limited columns and when I open the condensed version a like pops open. Then asking if I would like to update this workbook that is contains links to other data sources.
I have a worksheet that has two different years and I am trying to update a summary page that pulls the amount based on the year in the following formula.
Entering a formula which will allow me to expand my data set by interpolating between sets of x,y values. My data set is in 5-minute resolution and I would like to interpolate so that I can output a new set of x,y values in 1-minute resolution. Here is my example of what I have and what I would like my end result to be, and also I have attached a sample of my data set in the below excel file:
Original data set Date TimeTemperature oC 6/12/13 13:30 18.28 6/12/13 13:35 17.9 6/12/13 13:40 17.9
Desired data set Date TimeTemperature oC 6/12/13 13:30 y 6/12/13 13:31 y 6/12/13 13:32 y 6/12/13 13:33 y 6/12/13 13:34 y 6/12/13 13:35 y 6/12/13 13:36 y 6/12/13 13:37 y 6/12/13 13:38 y 6/12/13 13:39 y 6/12/13 13:40y
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
http://www.mrexcel.com/forum/showthread.php?t=350701. So far this is the closest I got, but it only pulls the values for 01 Nov. =NDEX(NS_Tally!W1082:W1360,MATCH($J$32,NS_Tally!V1082:V1360,0))
I've got a working macro that pulls a Userform. Everything works correctly but I'd like to have the form displayed on the far right instead of center screen. I've tried all the options on the form properties list to no avail.
Private Sub Workbook_Open() ActiveWindow.WindowState = xlMaximized Worksheets(1).Select Range("A1").Select UserForm_1.Show End Sub
I am trying to create a spreadsheet for all the different Myers Briggs personality types. I want to pull down on a menu say ISFJ and in the next column will produce how to communicate to a person who has an ISFJ personality.
I am trying to put together multiple userforms that perform separate tasks. I have been using code found on this forum, and it's helped out a lot, but i'm still having trouble on one of the userforms. the one that i'm having the biggest trouble with called frmEditData. What it is designed to do is pull data from different fields and display them in the textboxes on the different sheets. then, the user has the option of updating those tabs and saving the changes. For the life of me, i cannot get the data to change. it always reverts back to the original information in the cells.
i have to say again that I don't really know how to read the code too well just yet, and i've been copying code that appears to work on other sheets, but it might not necessarily work here. I have attached a rough copy of what i've been working on.
Is it possible to get Vlookup to pull the format of text across to the new cell?
For example, if the source data is bold, can you get it to show up as bold in the new cell? Or worse, if there are multiple formats in the original cell (like some text is bold, some is red font etc.) can that be pulled across through Vlookup?
I am trying to create a drop down box that pulls its selection from another sheet in the workbook and I'm not sure how to go about doing it. I figure VBA must have some way of doing it but I am not sure how the coding would go. I've attached a very basic example of what I am trying to accomplish.
I have a VBScript that pulls SNMP data from some devices. It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE". When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices). The script looks like this :
i have created a Vlookup that pulls data from another work sheet, it all works fine apart from this one thing....
On a work sheet i have a lot of hyperlinks that work fine, but as soon as they get pulled over using a Vlookup, the name of the hyperlink comes accross for example "link 1" but you can not click on it to take you to the link like you can on the original one.
I am looking at trying to do a complex rounding within Excel. I have a spreadsheet of all my product with my cost. I then convert to show what margin I want to sell my items at and then round to .99.
What I am looking at doing is getting my pricing more uniform so that instead of having 23.99, it is 24.99 or 20.99 it is 19.99. I want all the numbers in the ones' spot to round depending on a set parameter.
I have played around with if statements, but can not get it to work fully. Basically if the ONES number is 0 to 2 I want it to round down to 9.99 (ie first was 21.99 I want it to be 19.99) from 2.01 to 4.98 to round to 4.99 and then 5.00 to 9.98 round up to 9.99.
Is there a formula that will calculate standard 60 minute time in to 100 dinute time? Example 2:21 = 2:35. I know how to get to the answer with a calculator, but would like a formula to cut out all of the extra steps.
I am having problems with the function vlookup. I attached an excel file of my problem. It seems to pull the correct lookup, then it pulls the wrong data until a correct one occurs. My excel attachment should make more since of my problem. It will also pull data that is invalid. For example, the name "S5" is not a in my original data so it should not be able to find that?? Sorry, this is hard to explain - let me know if the file is not clear enough.
