Forum Discussion
Get historical high with StockHistory function
Try this: =MAX(STOCKHISTORY("TSLA","1/1/1950",TODAY(),0,0,3))
Let me know if that's what you were looking for.
Thanks for your help. The example works.
but I need to get the price for a list of stocks, so I made this formula
=MAX(STOCKHISTORY(A1.[Exchange abbreviation] & ":" & A1.[Ticker symbol],"1/1/1950",TODAY(),0,0,3))
It works for some stock but not all. Most return #N/A error.
I suspect that was because there was no data on 1950.
Is there a way to get from the earliest date that is available?
To return entire row for the max value you may use something like
=INDEX(SORT(STOCKHISTORY("TSLA",DATE(1950,1,1),TODAY(),,0),2,-1),1)Here is at the left
STOCKHISTORY() by default takes the earliest date if the range is wider. To illustrated, I took UBER which was listed in mid 2019, range starts from 1950.
Most probably errors are due to some other reasons.
Thanks Sergei,
I think the MAX() works fine. but it's good to know how to know when was it happened.
I am also thinking about how to calculate its annualized increase in price. getting the price of first-day listing and the current price. Divide them by the amount of time. Sounds like huge work for me.
To ignore errors
Simple MAX() will work the same way if you prefer to have only one column.
I am also thinking about how to calculate its annualized increase in price. getting the price of first-day listing and the current price. Divide them by the amount of time.
Afraid didn't catch what the exact idea is. "annualized" price mean price for the each year, another story is how to calculate. However, in next sentence you say about only two dates - start and current.
Since the formula is returning a single cell (thanks to the MAX function), I would just place my multiple tickers in a single column, A1 to A100 (whatever) and copy the formula down in all adjacent rows.
=MAX(STOCKHISTORY(A1,"1/1/1950",TODAY(),0,0,3))
Furthermore, given the general movement of the stock market upward, I can't image why you even need to go back to 1950 for any individual stock that is currently traded. That couldn't be the cause of the #NA error; after all TSLA worked and it didn't even exist until far more recently than that.
Something like that. I haven't tested it, but see no reason why that wouldn't work for symbols that are active.
I found out the #Value error occurred because there was an #N/A error for one of the dates, which I don't know why it happens. So I am trying to exclude errors in a set of data when calculating the MAX.
I made the following formula but it does not work. Could you help?
=AGGREGATE(4,6,STOCKHISTORY(D17,"1/1/1950",TODAY(),2,0,3))
Example stock: XNYS:FOUR
