Google-sheets – How to get AUD price of bitcoin at a particular date using GOOGLEFINANCE

As I was building this question, I figured out several ways to achieve this, so I went ahead and shared the information.

There are several ways to do this. The first is a variation on your original syntax, but using nested IF statements instead of IF and AND:

=ARRAYFORMULA(SUM(IF(MONTH($A$1:$A$5)=MONTH(E1), IF(YEAR($A$1:$A$5)=YEAR(E1), $B$1:$B$5))))

The second uses the FILTER function. This method will return a #N/A error if FILTER doesn't find any matches for the conditions. FILTER takes each condition as a separate argument:

=SUM(FILTER($B$1:$B$5, MONTH($A$1:$A$5)=MONTH(E1), YEAR($A$1:$A$5)=YEAR(E1)))

The third uses INDEX and SUMPRODUCT:

=INDEX(SUMPRODUCT((MONTH($A$2:$A$6)=MONTH(E2))*(YEAR($A$2:$A$6)=YEAR(E2))*$B$2:$B$6), 1)

In each of these examples, I assumed that the data were in columns A and B, the "pivot table" dates were in column E, and the aggregated data are placed in column F.

There might be a way to do this with the QUERY function that provides an interface to the Google Visualization API Query Language, but I'm not sure. I don't know if such a query would dynamically update, either.

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.