Get historical high with StockHistory function

[CALVIN2021]

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())

[#2]

@CALVIN2021 

Try this: =MAX(STOCKHISTORY("TSLA","1/1/1950",TODAY(),0,0,3))

Let me know if that's what you were looking for.

[#3]

@mathetes 

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? 

[#4]

@CALVIN2021 

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.

[#5]

@CALVIN2021 

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.

[#6]

@mathetes 

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

[#7]

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.

[#8]

@CALVIN2021 

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.

[#9]

@mathetes 

@Sergei Baklan 

OMG, just found out the max price is unadjusted. It doesn't take into account the effect on the price for share splitting. cry

[#10]

@CALVIN2021 

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.