How to insert historic exchange rates into Airtable

Anybody have any ideas on this?

So, if I need to log a receipt, and for example’s sake – the date is January 5, 2018,

I would like to automatically pull in the $USD to EURO exchange rate for that day.

I’ve tried various solutions and this is where I’m headed at the moment: Make another table in the base called “Rates” – and then automate a program to fill in the exchange rate each day. Then, have my “Transaction” table lookup the time and rate from there when I enter a receipt.
Is that even possible? You think this might work or a is it a bad, misguided idea?

Thanks AT Community for any help. :+1:t4:

If This, Then That — IFTTT.com — supposedly has a currency exchange rate function that can be used to generate a time-of-day triggered pull of any two currencies’ exchange rate – except I’ve never gotten it to work to the extent I could test it with Airtable. The last time I tried, it was configured to pull from Yahoo Stocks, apparently using a deprecated API, and attempts to configure the applet repeatedly bombed to a 500 server error. Today, its target seemingly has been rehomed to [Yahoo] Finances, but the applet construction attempt still hangs, albeit at a different spot. If they get it stabilized, it appears it could be [part of] the answer.

Thanks for the insight, Vann.
That sounds like a potentially good solution.
I’m going to take your concept and see if I can find something that will function similarly. I’ll keep you posted here if I do – and likewise, please post again if you find something.

Just FYI, I also opened a feature request on an admittedly edge case capability I think would greatly aid and enhance the use of such things as automated exchange rates.

Yes, thanks for mentioning that.

Ok, I have a simple way to bring the daily rates into Airtable.
So, I have a table with two fields = “Date” & “Rate” (almost poetic, don’t you think?)
A new record is entered automatically each day with the day’s exchange rate making a log with the data I can work with.

Now, here’s where I need a little help or a little more coffee:

Do I set up a conditional rollup or lookup (or something else) to bring the exchange rate over from the “Rate” table to my “Receipt” table – and how do I do that?

In other words, if I enter a receipt for 5 January 2018, what function would I use for Airtable to look up 5 January on the “Rate” table and pull in the corresponding exchange rate for that day?

There are two ways I can think of off the top of my head:

1. Convert the Date field on the Receipt table to be a linked field to your Rate table.
   This is the simpler approach and doesn’t require any new columns, but will require you to be careful when entering dates (and would prevent you from using the mini-calendar to enter dates).
2. Create a new linked field to your Rate table (again with the date as the record’s primary field).
   This will require an extra column (or two), and will require you to copy and paste the date into the linked field. However, with this approach, you can have a timestamp in your Date field (just use a formula field with DATETIME_FORMAT to get it in a form that works with your linked field), and it’ll also allow you to do things like have a transaction date separate from the exchange date (for example, if you do your currency exchange in batches).

As for getting the actual exchange data, I think you can set up a Zapier link with Google Drive/Sheets and use the built-in lookup (e.g. =GOOGLEFINANCE("CURRENCY:USDEUR","price","2018-01-01","2019-01-01","daily") ) and have it update your Rate table whenever it detects a change.

For anyone who still has this issue our app currency converter is designed to solve it very simply. (You can select a date column to ensure historic exchange rates are used) Currency Converter - Apps - Airtable Marketplace
We’d love to hear any feedback about how to make it better though! :slightly_smiling_face:

The Data Fetcher extension handles this well: