Stock Market Updating Data Keep a record of past data

[richard96]

I have live stock market data in excel, the price updates every few seconds. I have the "last" price in column D. I need a way to keep a second to last price so when it updates I save the previous price so I can compare them. I would also like to make a chart so I don't know if I need to keep the data further back then just the second to last price or if the chart will keep it itself. I want to make a candle chart of the data that lasts the whole day. Any help would be appreciated.

[vba_php]

IMO, the question isn't whether you can do this in excel, but the question rather is: why can't you do this on the stock market board's website? they all have trackers. NASDAQ, DOW JONES, S&P500, NYSE, etc....

[richard96]

I want to build a custom indicator based on soecific stocks that are moving up or down at any given time and i need 2 prices to compare to see which direcrion they are moving?

[vba_php]

so what have you tried? got a file to upload, that you have started? I can't help with charts, because I never use them. to me, they are useless. every person in the world is throwing massive amounts of data into corporate server databases every minute of every day because they're bored to death. thus, why even bother with charts? just read the data. you can read their soul that way, literally. that's what corporations have been doing for at least 5 years.

[richard96]

Yeah here is a sample of what I have started so Column A is all the s&p500 tickers. Column C is the RTD link from the Thinkorswim platform that brings in the last price of every stock. Column D is where i run out of Knowledge I don't know how to save a "second to last price" so when the Last price updates I can have the previous price to compare it to. In column E so I can tell whether the stock has moved up or down since its last movement. Then column G is just where the data points of up/down would go or 1 and -1 then column I is what this is all about I can see the Net number of companies going up or down at anyone time. I know how to get the rest done if i can just figure out how to save the previous "Last price".

I have some VBA code in there that I found online that I tried to make work. I don't know enough about it to tell if it is close to what I need or not I can not manage to get it to work though? thanks for responding

[vba_php]

you have a sub called Worksheet_Change. that event is triggered when cells on the worksheet are changed by the user or by an external link. per this: https://docs.microsoft.com/en-us/off...rksheet.change

so, obviously that will work for you because your link IS an external link. furthermore, F9 on the keyboard has always updated calculations in an entire book. that's been the case forever, I believe. but at the moment, your code does nothing. if you want to learn:

to test what triggers the change event, I put fake data into your sheet, double clicked in one of the cells and pushed the enter button. and I saw the message that you see in image 1 below. image 2 shows the code that I used. so, you can take that inevitable process, learn from it, then write the following code to get what you want in col ''D'':

Please Login or Register  to view this content.

So, obviously you will have to change this a bit, because your data is coming in dynamically. you need a way to find a trigger for that.
and obviously the excel-based trigger for it would be the worksheet change event. but, if that is so, you can't do what I've written technically,
because that would throw col b data into col c AFTER your data would be refreshed. and thus, both columns of data would be the same.
to remedy this problem, store the data that comes in, on the change event that I've just mentioned, in another column.
that way, the code that runs, as in my example, can throw data from that temp column (which would be the last price, from the last update),
into col d. then your cur data would be in col c. done. and you can repeat that process forever. see attached book for a sample i gave you.
I did everything except col I. don't know what you mean by points, nor will I look at it. i'm not in the stock business. hope this helps.

[richard96]

Thanks that looks like it should work great but I still can not seem to get it to work with the dynamic data feed. I tried changing the columns, do I need to write another line to do what you said about storing the data from the change event in a different column? When I change the column letters it looks like it tries to work but then it just gives me an error and crashes.

[vba_php]

post an image of the error you see.
post the code you are runnning and behind what event the code runs
if possible, post the URL of the website you are pulling stock data from.

[richard96]

The data comes form the Thinkorswim Platform this is the link I use to get the last price =RTD("tos.rtd",,"LAST",A2). Here is the pictures of the error I see when I try to run the code by changing the letters I have tried multiple ways of changing the columns. The code is almost the same as yours you sent but i changed one of the columns to try and get it to store the data somewhere else like you said .

Code
(Private Sub Worksheet_Change(ByVal Target As Range)
'this routine is given to richard to show his employer that he's now a genius.
Range("b3", Range("c3").End(xlDown)).Select
Selection.Copy
Range("d3").Select
ActiveSheet.Paste
End Sub)

[vba_php]

there's no reason for that error to occur. it works fine for me over here. see image below for the result of replica code that I ran that had the exact result it should have.

[richard96]

I don't know if i am doing something wrong but it keeps giving me the same error a runtime error it seems like it is trying to copy and paste infinitely and glitches itself out the 2 columns like c/d just turn grey back and forth until it says runtime error?
I tried starting a fresh sheet with no number or external links and it does the same thing what could i be doing wrong or does it need a code to tell it when to stop so it doesn't keep copying and pasting forever?

[vba_php]

i got the same error after simulating it over here again. it must be because there is an empty col on the left side. that's all i can think of.