Skip to main content
Web Applications

Return to Answer

replaced http://webapps.stackexchange.com/ with https://webapps.stackexchange.com/
Source Link
Community Bot
Community Bot
  • 1

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 NormalNormal 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

using dd/mm/yyyy as date format works fine

#Reference

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

using dd/mm/yyyy as date format works fine

#Reference

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

using dd/mm/yyyy as date format works fine

#Reference

added 123 characters in body
Source Link
Rubén - Volunteer Moderator - ♦
Rubén - Volunteer Moderator - ♦
  • 46.1k
  • 18
  • 100
  • 297

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

using dd/mm/yyyy as date format works fine

#Reference

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().

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

using dd/mm/yyyy as date format works fine

#Reference

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

using dd/mm/yyyy as date format works fine

#Reference

Update due to reviewing of the demo spreadsheet.
Source Link
Rubén - Volunteer Moderator - ♦
Rubén - Volunteer Moderator - ♦
  • 46.1k
  • 18
  • 100
  • 297

#Short answerUPDATE: InsteadThe #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().

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

using dd/mm/yyyy as date format works fine

#Reference

#Short answer 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

using dd/mm/yyyy as date format works fine

#Reference

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().

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

using dd/mm/yyyy as date format works fine

#Reference

added 159 characters in body
Source Link
Rubén - Volunteer Moderator - ♦
Rubén - Volunteer Moderator - ♦
  • 46.1k
  • 18
  • 100
  • 297
Source Link
Rubén - Volunteer Moderator - ♦
Rubén - Volunteer Moderator - ♦
  • 46.1k
  • 18
  • 100
  • 297