Google-sheets – How to pulling in historical daily trade volume data in Google Sheets with Google Finance

formulasgoogle sheetsgoogle-finance

If I try =GOOGLEFINANCE(A1,"volume",A2) where A1 is the stock code and A2 is the date I'm querying, it comes back printing 'date' and 'volume' on one line as headers and then the data on the line below. Is there a way to have this formula printing on each line without the headers?

e.g. GOOG

A1  13/06/20  B1 daily volume  
A2  12/06/20  B2 daily volume
A3  11/06/20  B3 daily volume

I'm just trying to build the formula that can give the data for B1,B2,B3 etc and drag it down so the data is on each line querying the respective date in column A.

What formula do I need to use?

Best Answer

It's rather easy to get a list of data: just add the number of days as a parameter, e.g.

=GOOGLEFINANCE(A1,"volume",A2,30)

Specifying start and end date is also possible, see the documentation.

If you want it for specific (non-consecutive) dates, I'd suggest to use VLOOKUP or some other query method. An example:

The Finance formula is now =GOOGLEFINANCE(A1,"volume",MIN(A2:A100),MAX(A2:A100)) and the second column is =VLOOKUP(A2+TIME(16,0,0),D:E,2,FALSE); adding 16 hours is one possible way to make sure it matches the timestamp in the 'Date' column.

Formula

=QUERY(DATA!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B")

Screenshot

enter image description here

This is a good alternative, but formula wise a bit different.

Formula

=QUERY(DATA!A:C, "SELECT A, B, SUM(C) WHERE A IS NOT NULL GROUP BY A, B   
   LABEL SUM(C) 'Costs'")

Screenshot

enter image description here

Example

I've added the results into your dummy sheet.

Google-sheets – How to add refreshed data to new column in Google Sheets

Whenever it refreshes, the data is copied as opposed to new data being added. It seems like it is adding the formula itself to the spreadsheet as opposed to putting in the value itself.

If I understand correctly, you want the columns to keep a "snapshot" of the prices at the time the column was added, rather than have the values continue updating.

In order to do this, we can replace the formulas with the calculated values at the time to "freeze" the values:

// select the range you want to "freeze":
var range = SpreadsheetApp.getActiveSheet().getRange('B:B');

// replace the cell formulas with the calculated values:
range.setValues(range.getValues());

I manually tested this with the GOOGLEFINANCE function and it did replace the function with the current price as a numerical value. You could add this to the end of your function to "freeze" the prices after getting them.

Another solution might be to use the start_date parameter on the GOOGLEFINANCE function. You could get the current date as a value and then pass that into the formula as the start_date so that would always fetch the price for that date.

Best Answer

Related Solutions

Google Sheets – How to Filter, SUM, and Display Multiple Values with Conditions

Formula

Screenshot

Formula

Screenshot

Example

Google-sheets – How to add refreshed data to new column in Google Sheets

Related Topic