google-sheets

GoogleFinance: Return Data Without Column Header


In a Stocks spreadsheet, I now need to get the AUDUSD currency rate for a particular date, so that I can assess the exchange rate gain/loss as well as stock gain/loss.

To get the specific date, I used:

=QUERY( GOOGLEFINANCE("CURRENCY:AUDUSD", "price", DATE(2021,7,1), DATE(2021,7,2), "DAILY"), "SELECT Col2" )

However, this returns the column header "Close" as well as the value, so I tried using OFFSET in the QUERY statement:

=QUERY( GOOGLEFINANCE("CURRENCY:AUDUSD", "price", DATE(2021,7,1), DATE(2021,7,2), "DAILY"), "SELECT Col2 OFFSET 1" )

Oddly, this returned just the header, without the value. I haven't been able to find the Google documentation for QUERY(), so I don't know what I'm doing wrong.

Any suggestions, or pointers to the docs, would be most appreciated.


Solution

  • Using label in Query we can rename the header of data, here we rename with blank hence it returns only the value of price from col2

    =QUERY(GOOGLEFINANCE("CURRENCY:AUDUSD", "price", DATE(2021,7,1), DATE(2021,7,2), "DAILY"),"select Col2 label Col2''")

    QUERY