May 25, 2019

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.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.

### 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))

### 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))

References: