Google-sheets – Stock Sparkline for today/hourly

google sheetsgoogle-finance

I am trying to make a sparkline graph in Google Sheets that tracks the stock price of TODAY using google finance as time progresses. For example, at 8am it would show the opening price, and by close there would be a graph of that days rise/fall progress. I'm wondering if this is possible, as I've tried many variations of the following formula (which I am using for 3, 7, 14, 30, and 1 year graphs), but I cannot seem to integrate the TIME(), TODAY(), or NOW() at all and don't know how to move forward from here.

Here is the formula for my other sparklines:

=SPARKLINE(GOOGLEFINANCE($F6,"price",TODAY()-1,TODAY(),"DAILY"),{"charttype",K12;"linewidth",2;"color","#6d4600"})

…where F6 is the stock ticker, K12 is "line", and the -1 is changed to the timeline I want for the graph (ie. -7 is a week.) Anyone have any ideas to make this work?

Best Answer

GoogleFinance function does not provide hourly data (source: its documentation). For today, one can only get the current (delayed) price, opening price, and the high and low marks so far. Can't make a sparkline out of this.

For an interval of several days, one can make a sparkline as follows:

=sparkline(query(googlefinance("GOOG", "price", today()-7, today()), "select Col2 label Col2 ''", 1))

For historical data, googlefinance returns a table. The query selects the second column of the table (prices) and omits its header (`label Col2 ''). Then the sparkline is created out of that.

Related Solutions

Google Sheets – How to Create a Script for Reporting 26-Week Highs

I'll describe the process in stages. Here is a command that returns a table with high price on every day within the last 26 weeks:

=GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY())

Output:

+-------------------+----------+
|       Date        |   High   |
+-------------------+----------+
| 2/2/2015 16:00:00 | 533      |
| 2/3/2015 16:00:00 | 533.4    |
| ................. | .......  |

Since you only want the maximum of these numbers, let's add a QUERY selecting the maximum of the second column:

=QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select max(Col2)")

Output:

+----------+
| max High |
+----------+
| 678.64   |
+----------+

This may be already good enough for your purposes. If you want to get rid of the column header "max High", end the query string with label max(Col2) '':

=QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select max(Col2) label max(Col2) ''")

Output:

+----------+
| 678.64   |
+----------+

Google Sheets – Automatically Store Updated GOOGLEFINANCE Values with Script

Functions like GOOGLEFINANCE are only updated when the spreadsheet is open by a user, there isn't a Google Apps Script method that is able to do this. The closest is SpreadsheetApp.flush() but this only makes that the changes made by the script be pushed to the spreadsheet.

One alternative is to rethink your model and take advantage that ...

GOOGLEFINANCE already offer a way to report historic data. The syntax is

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Example

The following formula returns the daily close values of NASDAQ:GOOG from January 1, 2017 to today.

A1:

=GOOGLEFINANCE("NASDAQ:GOOG","price","1/1/2017",TODAY())

The following formula returns the daily close values of NASDAQ:AMZN from January 1, 2017 to today.

D1:

=GOOGLEFINANCE("NASDAQ:AMZN","price","1/1/2017",TODAY())

To calculate the daily average, we could not use AVERAGE with ARRAYFORMULA but we could use the + and / operands:

G1:

=ArrayFormula((B2:B+E2:E)/2)

Note:
Suggestion: Delete the blank rows at the bottom in order to make the calculation of the daily average just for the rows with data.

The history daily average will be calculate from the start date to the actual date every time that the spreadsheet be recalculated.

Result (extract):

Date               Close        Date               Close        Average
 1/3/2017 16:00:00  786.14       1/3/2017 16:00:00  786.14      786.14
 1/4/2017 16:00:00   786.9       1/4/2017 16:00:00   786.9       786.9
 1/5/2017 16:00:00  794.02       1/5/2017 16:00:00  794.02      794.02
 1/6/2017 16:00:00  806.15       1/6/2017 16:00:00  806.15      806.15
 1/9/2017 16:00:00  806.65       1/9/2017 16:00:00  806.65      806.65
1/10/2017 16:00:00  804.79      1/10/2017 16:00:00  804.79      804.79

Note:
Google spreadsheet functions are only recalculated while the spreadsheet is open, so using a script to be ran while the spreadsheet is not opened by anyone will retrieve the values saved the last time the spreadsheet was online-opened/synced offline changes.

References