STOCKHISTORY or other formula

[Gregg Wolin]

I'd like a to create a dynamic table of historic stock prices based on a list of dates and ticker symbols. The STOCKHISTORY function creates a dynamic output that includes a date column so I cant create a table like the one below and the stock function in the data tab is very cumbersome. Is there a formulaic way to populate such a table?

Date	NNN	EPRT	O
1/1/2021	$50	$40	$88

 

[p45cal]

The STOCKHISTORY function creates a dynamic output that includes a date column

Yes, it does:



but you can lose a column (drop the 1st column): edit: BUT SEE LATER.



if you don't have the DROP function you can use INDEX (take the 2nd column):



See also CHOOSECOLS and TAKE.

Edit: Just noticed the 6th (and later) parameters of STOCKHISTORY lets you decide which column(s) to show:



See help here:

STOCKHISTORY function - Microsoft Support

support.microsoft.com

 

[Gregg Wolin]

Yes, it does:

View attachment 84164

but you can lose a column (drop the 1st column): edit: BUT SEE LATER.

View attachment 84165

if you don't have the DROP function you can use INDEX (take the 2nd column):

View attachment 84166

See also CHOOSECOLS and TAKE.

Edit: Just noticed the 6th (and later) parameters of STOCKHISTORY lets you decide which column(s) to show:

View attachment 84167

See help here:

STOCKHISTORY function - Microsoft Support

support.microsoft.com

Thanks but this doesn't create the table i am looking for. I probably should have provided a more detailed example. I just want a single column of dates with the closing prices in the columns to the right based on the ticker symbol:

Date	NNN	EPRT
1/1/2021	$50	$60
1/7/2021	$54	$63
1/14/2021	$55	$61

 

[p45cal]

A bit wasteful to have STOCKHISTORY return only one date, but it can: