STOCK HISTORY FUNCTION OPEN PRICE FOR ONE DATE IN ONE CELL ONLY

[dattmuffy15]

HOW DO I GET THE STOCK PRICE FOR MICROSOFT MSFT 5 YEARS AGO @ OPEN OR CLOSE IN ONE CELL ONLY WITHOUT THE OBNOXIOUS ARRAY.

HOW IS ONE OF LARGEST MARKET CAPS ON EARTH NOT USER FRIENDLY ?

[#2 · Accepted solution]

@dattmuffy15 

The date 5 years ago could be not trading date. For such dates stock prices are not published, you need to take latest previous trading date. Using STOCKHISTORY that could be

=TAKE( STOCKHISTORY("MSFT", EDATE(TODAY(),-12*5)-10, EDATE(TODAY(),-12*5),0,0,1), -1)

which returns one-cell value for the closest trading date 5 years ago.

Microsoft Excel is not specialized software to work with market prices. It gives some basics and lot of other functions around. Combining them you could receive desired result.

Instead of hardcoding date, close or open price, etc., you may write your own function to parametrise about depends on your own needs.

[#3]

@SergeiBaklan 

OK THANKS.

IT WORKS BUT WILL YOU PLEASE SHOW ME HOW FOR 500 STOCKS I WATCH ?

I CAN GET IT TO WORK BUT I'D HAVE TO TYPE IN ALL OF THEM MANUALLY. 

HERE IS A FILE WITH THE EXAMPLE 

[#4]

@dattmuffy15 

It's not necessary to hardcode the ticker, use reference on Stock card instead

=TAKE( STOCKHISTORY($A2, EDATE(TODAY(),-12*5)-10, EDATE(TODAY(),-12*5),0,0,1), -1)

and drag it down. In above errors are returned for the companies incorporated later than 5 years ago. You may wrap above formula with =IFERROR(,..."no data") or like.

For the ticker you also may use =A2.[Ticker symbol]

[#5]

THANK YOU VERY MUCH FOR THIS.  @SergeiBaklan 

[#6]

@dattmuffy15 , you are welcome