1

I'm trying to write a formula to give the average price of a stock between two specific dates in the past in Google Sheets Finance, say between October 3rd, 2013 and November 12th, 2013.

If anyone can help, it would be much appreciated.

Thanks, Nick

Nick
  • 29
  • 1
  • 4

2 Answers2

1

try:

=AVERAGE(INDEX(QUERY(GOOGLEFINANCE("TSL"; "price"; "2021/10/03"; "2021/11/12"); 
 "offset 1"; 0);;2))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Does the formula apply for volume too? When I tried it with the "volume" attribute for the stock ticker "LULU" for the period 2008-01-04 to 2008-01-30, it doesn't match the volume data from Investor.com. Oddly, using your formula for volume data for "GOOGL" for the same period, 2008-01-04 to 2008-01-30, looks to be accurate. – Nick Oct 25 '21 at 20:08
  • @Nick not all tickers work in GOOGLEFINANCE. also there are some tickers which does not have that long history – player0 Oct 25 '21 at 20:28
0

First you take data from that period. For example: =googlefinance("CURRENCY:EURUSD","price","2013-10-03","2013-11-12","daily")

This builds a table where you have all the rates in 2nd column. Then you have to take only second column using INDEX() formula and take average of it using AVERAGE() formula:

=average(index(googlefinance("CURRENCY:EURUSD","price","2013-10-03","2013-11-12","daily"),,2))

enter image description here

Krzysztof Dołęgowski
  • 2,583
  • 1
  • 5
  • 21