10

I use GOOGLEFINANCE() to query the historical USD/GBP exchange rate at a set of fixed dates.

This works fine, except sometimes GOOGLEFINANCE returns #N/A, for whatever temporary upstream reason. When this happens, my spreadsheet becomes filled with #REF's, for all cells that depend on these exchange rates. The sheet is unreadable until the upstream data source is fixed. This can sometimes take hours.

This happens frequently and is especially annoying since I'm not using GOOGLEFINANCE to retrieve time-varying data. I'm just using it as a static dataset of historical exchange rates, so theoretically I have no reason to refresh the data at all.

Is there a way to locally cache the historical exchange rates in my sheet, and to fall back on those values if GOOGLEFINANCE returns #N/A?

(Bonus points for automatically updating the cached values if GOOGLEFINANCE changes its mind about the historical exchange rates.)

player0
  • 124,011
  • 12
  • 67
  • 124
SuperElectric
  • 17,548
  • 10
  • 52
  • 69

1 Answers1

-1

well, you are working with historical data eg. those data won't change no matter what so you can get the data you need and just hardcode them eg. get rid of the GOOGLEFINANCE for good.

another way would be to wrap any possible #REF! into IFERROR so when the blackout occurs you will get nice blank sheet instead of the sea of #REF! errors

player0
  • 124,011
  • 12
  • 67
  • 124
  • 2
    The question is how to locally copy these values when GOOGLEFINANCE is working, without resorting to manual transcription. – SuperElectric Apr 29 '19 at 22:11