Analyze Historical Stock Price With Google Sheets: GOOGLEFINANCE Example

May 25, 2019

Use GOOGLEFINANCE.

I use Google Sheets to analyze the following historical price

  • Current Price
  • Average Price for last 3D, 7D, 14D, 30D, 90D, 180D, 1Y, 2Y, 3Y (Averge window size could be adjusted)
  • Max and Min Price for specificed period: last 30 days, 90 days, 180 days, 1 year, 2 years, 3 years
  • Use conditional formatting to indicate positive (blue) and negative (red) changes

Google Sheets GOOGLEFINANCE

Get current/today price

=GOOGLEFINANCE("GOOG","price")

Output

1133.47

NOTE: Refer to the GOOGLEFINANCE documentation to get other informations like high, low, volume, pe (The price/earnings ratio), high52 (The 52-week high price), etc.

=GOOGLEFINANCE("GOOG","pe")

Output

28.43

Price for specific day

Yesterday - 1 day ago’s price

=GOOGLEFINANCE("GOOG","price", TODAY()-1)
Date    Close
24/05/2019 16:00:00 1133.47

3 Days ago

=GOOGLEFINANCE("GOOG","price", TODAY()-1)
Date    Close
22/05/2019 16:00:00 1151.42

1 year ago

=GOOGLEFINANCE("GOOG","price", TODAY()-365)
Date    Close
25/05/2018 16:00:00 1075.66

Specific date

=GOOGLEFINANCE("GOOG","price", "2019-05-01")
Date    Close
01/05/2019 16:00:00 1168.08

NOTE: If the day is a holiday (non-trading day), result of the next trading day shall be returned

Single cell result

By default, the output occupy four cells (2x2). To get the price into single cell, use

=INDEX(GOOGLEFINANCE("GOOG","price", "2019-05-01"), 2, 2)
1168.08

NOTE: Refer INDEX

Price for last N days

Last 3 days

=GOOGLEFINANCE("GOOG","price", TODAY()-3, 3)
Date    Close
22/05/2019 16:00:00 1151.42
23/05/2019 16:00:00 1140.77
24/05/2019 16:00:00 1133.47

Date Range

=GOOGLEFINANCE("GOOG","price", "2019-05-01", "2019-05-10")
01/05/2019 16:00:00 1168.08
02/05/2019 16:00:00 1162.61
03/05/2019 16:00:00 1185.4
06/05/2019 16:00:00 1189.39
07/05/2019 16:00:00 1174.1
08/05/2019 16:00:00 1166.27
09/05/2019 16:00:00 1162.38

Note that the 4th arguments of GOOGLEFINANCE, which is [end_date|num_days]: if num_days is used, maxium value to be used is 50. If I put in 51, the result returned is from date until today.

=GOOGLEFINANCE("GOOG","price", "2019-01-01", 50) will return 50 results.

=GOOGLEFINANCE("GOOG","price", "2019-01-01", 51) will return results from 2019-01-01 until today.

NOTE: I am not sure if this a persistent bug or related to OS/Browser limit.

Solutions for 50 days limit is something like =GOOGLEFINANCE("GOOG","price", "2019-01-01", DATE(2019, 1, 1)+60).

Note that there are difference between

  • =GOOGLEFINANCE("GOOG","price", "2019-01-01", 50) - 50 results from 02/01/2019 to 14/03/2019
  • =GOOGLEFINANCE("GOOG","price", "2019-01-01", DATE(2019, 1, 1)+50) - 33 results from 02/01/2019 to 19/02/2019

Merge result into single cell

This will generate 50+1 rows

=GOOGLEFINANCE("GOOG","price", "2019-01-01", 50)
Date    Close
02/01/2019 16:00:00 1045.85
03/01/2019 16:00:00 1016.06
04/01/2019 16:00:00 1070.71
07/01/2019 16:00:00 1068.39
...

Extract price only

=INDEX(GOOGLEFINANCE("GOOG","price", "2019-01-01", 50), ,2)
Close
1045.85
1016.06
1070.71
1068.39
...

Calculate average

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price", "2019-01-01", 50), ,2))
1106.6056

NOTE: You can also use MEDIAN, MIN, MAX, etc.

Advance Example

Last N days

Prices for last 3 days

=GOOGLEFINANCE("GOOG","price", TODAY()-3, TODAY())

Average of Last 3 days

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-3, TODAY()), ,2))

1 week ago

Prices for 1 week ago (7th-14th days ago)

=GOOGLEFINANCE("GOOG","price", TODAY()-14, TODAY()-7)

1 week ago average

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-14, TODAY()-7), ,2))

1 month ago

Prices for 1 month ago (30th - 60th days ago)

=GOOGLEFINANCE("GOOG","price", TODAY()-60, TODAY()-30)

Average price for 1 month ago

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-60, TODAY()-30), ,2))

This year

Prices for this year (last 365 days).

=GOOGLEFINANCE("GOOG","price", TODAY()-365, TODAY())

or

=GOOGLEFINANCE("GOOG","price", "2018-01-01", "2018-12-31")

Get Average, Min & Max price for this year

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-365, TODAY()), ,2))
=MIN(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-365, TODAY()), ,2))
=MAX(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-365, TODAY()), ,2))

1 year ago

Prices for 1 year ago (365th day - 730th day)

=GOOGLEFINANCE("GOOG","price", TODAY()-(365*2), TODAY()-365)

Get Average, Min & Max price for 1 year ago

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-(365*2), TODAY()-365), ,2))
=MIN(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-(365*2), TODAY()-365), ,2)
=MAX(INDEX(GOOGLEFINANCE("GOOG","price", TODAY()-(365*2), TODAY()-365), ,2)

References:

This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.