Skip to content

Instantly share code, notes, and snippets.

@shentonfreude

shentonfreude/gsheet-formula-eur-usd-cell-on-date.txt

Last active April 24, 2020 22:32
Show Gist options
  • Save shentonfreude/967eea264bea0e0801c23a31a00c1bc1 to your computer and use it in GitHub Desktop.
Save shentonfreude/967eea264bea0e0801c23a31a00c1bc1 to your computer and use it in GitHub Desktop.
Download ZIP
Convert Google Sheet cells from EUR to USD on given date
Raw
gsheet-formula-eur-usd-cell-on-date.txt
A B C D E
Date (Euro) Item EUR EUR:USD USD
24/3/2019 ExpenseForThing -35,00 1,12965 -39,54
D: Daily rate at mkt close: =index(GOOGLEFINANCE("CURRENCY:EURUSD";"close";A2;1);2;2)
E: Daily rate times cell C3: =index(GOOGLEFINANCE("CURRENCY:EURUSD";"close";A3;1);2;2)*C3
Note: this is for a File->Spreadsheet Settings -> Locale: Spain
For US Locale, you have to use commas in the forula rather than semicolons.
The index() is because even a single day query gives back 2x2 cells with Date, Value headers so we have to pick out the numeric value
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment