Skip to content

Instantly share code, notes, and snippets.

@m8rge

m8rge/query google finance.md

Last active November 1, 2022 11:20
Show Gist options
  • Save m8rge/b808084b4d47f26337746b3861bcc55d to your computer and use it in GitHub Desktop.
Save m8rge/b808084b4d47f26337746b3861bcc55d to your computer and use it in GitHub Desktop.
Download ZIP
Fetching currency for selected date in google sheets
Raw
query google finance.md

Fetching currency for selected date in google sheets

Google offers great abilities for fetching currency value from google finance:

=GOOGLEFINANCE("USDEUR")

will fill cell with most recent value for this currency.

0.982705

But what if we want to load history information for some specific date?

=GOOGLEFINANCE("USDEUR";"price";"5/22/2022")

It turns out that this query will not populate single but 4 cells:

Date Close
5/22/2022 23:58:00 0.94613

In most cases this is not what you expect. So, how we can get single cell filled with currency? I found a way:

=QUERY(GOOGLEFINANCE("USDEUR";"price";"5/22/2022");"Select Col2 offset 1"; 0)
0.94613

We have to use Query function. Query actually is pretty simple: just select second column an skip titles row. In a fact, knowing about column identifiers in such case is missing in official Query Language Reference. I found mention about this behavior at StackOverflow. God bless StackOverflow.

Using such query allows us to build a list currencies related to particular date:

Date USD/EUR
3/22/2022 0.9068
4/22/2022 0.92598
5/22/2022 0.94613
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment