Google-sheets – Google Finance function returns Date not Currency Conversion

googlegoogle sheetsgoogle-finance

I am trying to calculate the USD:AUD conversion from M3 and show the result of the current conversion rate in the next cell, but it is only resolving the date

I am trying to get cell M3 to convert to AUD in cell N3

=M2*GOOGLEFINANCE("CURRENCY:USDAUD")

This formula has worked in the cell above and converted the "live" rate, but when I amend it for other cells it throws up a random date such as 27/01/1900.

I have another part of the spreadsheet which converts the currency on a particular date, this however needs to be current and update itself with the live prices whenever I check it.

anyone able to help?

Best Answer

When it throws up a random date, try selecting the cell and choosing Format → Number → Number.

Related Solutions

Google Sheets – Automatic Currency Conversion

The value of GoogleFinance("CURRENCY:USDBRL") is the value of one US dollar in Brazilian currency. This needs to be multiplied by the dollar amount that you want to convert. So, if B5 is the USD amount, the formula

=B5*GoogleFinance("CURRENCY:USDBRL")

will give the equivalent amount in Brazilian currency.

Google-sheets – INDEX-formula throws random #REF!-errors due to [illogical] column value out of range

UPDATE: The #REF! errors were not returned randomly in a broad sense, they appear sometimes and once the first occurrence appear on one cell then it repeats to the cells below it.

This looks to be a latency problem inherent to GOOGLEFINANCE(), as was previously mentioned by Normal in a comment to the question.

In order to have a "rock-solid formula" instead of using GOOGLEFINANCE directly in column E use an auxiliary sheet to hold the historical currency conversion rates as fixed values instead of calculated values through a formula.

You could use GOOGLEFINANCE() to get that data but then copy and paste values only.

Short answer before the OP shared a demo spreadsheet

Instead of using ambiguous date format (00/00/0000) use a explicit one like

dd/mmm/yyyy
mmm/dd/yyyy

among others

Explanation

The problem could be due to the regional settings / date format of your spreadsheet / data.

I just tried to reproduce the problem but it works fine in my environment:

Date format: dd/mm/yyyy
Regional settings: Mexico
Account language: Spanish Latin America
Always show the function names in English

Reference

GOOGLEFINANCE - Google Docs editors Help