7

I have built a spreadsheet to help me keep track of my spending during my holidays. Whichever currency I buy in, it converts the raw amount into my local currency (Malaysian Ringgit MYR). Here is how it looks:

spreadsheet screenshot

I am using a combination of INDEX and GOOGLEFINANCE for this. Here is the exact code used inside cell E3:

=IF(D3="MYR",1,IF(C3="","",INDEX(GoogleFinance(CONCATENATE("CURRENCY:",D3,"MYR"),"price",B3,B3+1),2,2)))

When copying down, it seems the INDEX-function is inconsistent and throws random #REF! errors with the following error message:

Error
Function INDEX parameter 2 value is 2. Valid values are between 0 and 1 inclusive.

The thing is that there is a row 2 and column 2 for the googlefinance currency list that is referenced. I can't figure out why it is not working properly. If I reload and/or change some figures, it randomly works on a few rows. I would like a rock-solid formula that works at all times. An ideas on how to fix this?

Here is a link to a demo-sheet that is publicly editable. Play around to your hearts content! https://docs.google.com/spreadsheets/d/1SaMEfu-CpkA-m8S0V68ID9Xn5KD5Hdfk3s2vrGkhiNs/edit?usp=sharing

Improve this question
3
  • 1
    In order to be sure of what is happening with your spreadsheet, consider to create a demo spreadsheet, share it with anyone with the link for view only and add the link to your question. An alternative is to add more details like the spreadsheet settings and cell formatting of the cells with dates.
    – Rubén - Volunteer Moderator - ♦
    Commented Jan 10, 2016 at 17:55
  • 1
    The fact that it sometimes works and sometimes doesn't points to latency issue in obtaining the stock prices. I suggest revising the sheet so that the prices are imported with googlefinance separately, and the rest of the computation works with that table.
    – user79865
    Commented Jan 11, 2016 at 2:32
  • Thanks for your input. Here is a link to a demo-sheet that is publicly editable. Play around to your hearts content! docs.google.com/spreadsheets/d/…
    – Henrik Söderlund
    Commented Jan 12, 2016 at 4:18

1 Answer 1

Reset to default
5

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

Improve this answer
6
  • On the other hand, the Date column on the screenshot is right-aligned, indicating it's indeed recognized as a date (not a string). In this case there can be no ambiguity: the displayed format is just for human consumption, internally the date is the number of days since Day 0.
    – user79865
    Commented Jan 10, 2016 at 18:39
  • 1
    @Normal: what if the horizontal alignment was customized?
    – Rubén - Volunteer Moderator - ♦
    Commented Jan 10, 2016 at 18:45
  • 1
    That's possible. I upvoted your comment about giving us a demo spreadsheet...
    – user79865
    Commented Jan 10, 2016 at 18:46
  • The date column has a proper date format applied, it is not ambiguous. See demo link in comment above.
    – Henrik Söderlund
    Commented Jan 12, 2016 at 4:19
  • 1
    @HenrikSöderlund The dates were considered ambiguous because the OP have shared only a screenshot and we could not be sure if the horizontal alignment was customized :)
    – Rubén - Volunteer Moderator - ♦
    Commented Jan 12, 2016 at 12:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.