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
Get current/today price
=GOOGLEFINANCE("GOOG","price")Output
1133.47NOTE: 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.43Price for specific day
Yesterday - 1 day ago's price
=GOOGLEFINANCE("GOOG","price", TODAY()-1)Date Close
24/05/2019 16:00:00 1133.473 Days ago
=GOOGLEFINANCE("GOOG","price", TODAY()-1)Date Close
22/05/2019 16:00:00 1151.421 year ago
=GOOGLEFINANCE("GOOG","price", TODAY()-365)Date Close
25/05/2018 16:00:00 1075.66Specific date
=GOOGLEFINANCE("GOOG","price", "2019-05-01")Date Close
01/05/2019 16:00:00 1168.08NOTE: 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.08NOTE: 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.47Date 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.38Note 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 from02/01/2019to14/03/2019=GOOGLEFINANCE("GOOG","price", "2019-01-01", DATE(2019, 1, 1)+50)- 33 results from02/01/2019to19/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.6056NOTE: 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: