Jan 09 2021 11:57 AM
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())
Jan 09 2021 01:56 PM
Try this: =MAX(STOCKHISTORY("TSLA","1/1/1950",TODAY(),0,0,3))
Let me know if that's what you were looking for.
Jan 10 2021 01:25 AM
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?
Jan 10 2021 05:42 AM
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.
Jan 10 2021 11:34 AM
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.
Jan 10 2021 11:39 AM
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
Jan 10 2021 11:48 AM - edited Jan 10 2021 11:55 AM
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.
Jan 10 2021 12:11 PM
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.
Jan 10 2021 12:45 PM
OMG, just found out the max price is unadjusted. It doesn't take into account the effect on the price for share splitting. cry
Jan 10 2021 02:31 PM
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.