Forum Discussion
Get historical high with StockHistory function
HI all,
Since the stock data types can only get 52 weeks high, I turned to see if Stockhistory function can get the job done. However, with that function, I get a whole list of data, how can I turn this to be a simple "one cell, one formula, returning one value" format?
(I prefer not to use VBA and I'm using Mac)
=STOCKHISTORY("TSLA","01-01-1950",TODAY())
9 Replies
Try this: =MAX(STOCKHISTORY("TSLA","1/1/1950",TODAY(),0,0,3))
Let me know if that's what you were looking for.
OMG, just found out the max price is unadjusted. It doesn't take into account the effect on the price for share splitting. cry
You wrote: OMG, just found out the max price is unadjusted. It doesn't take into account the effect on the price for share splitting.
Not true: try entering this formula -- giving yourself lots of room.
=STOCKHISTORY("AAPL",DATE(1980,1,1),TODAY(),,,0,3)
The prices are most assuredly adjusted for splits.
So wipe off those tears.
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.
