Exchange rates, and setting up tables to use them

Hi Codans!

As part of our project management system, I need to be able to calculate an amount using foreign exhange rates on a particular date.

I’ve made a google sheet that pulls in the exchange rate data once a day, and I use zapier to send it over to a coda table. That gives me the rates needed. Win. (Its not perfect - you can see in the graph that there’s been interruptions to the data… USD for instance doesn’t give the data on the weekends, and something strange happened wednesday… but I can fix that!)

Here’s the table I’m using (its setup is important to my question…)

My question is what formula can I use to find an exchange rate that is specified in a row of a table.
Ie : my table has the date needed, and the exchange rate code (say, USD) as a lookup. However, I cannot for the life of me work out how to do it - without perhaps a massive nested if statement.

I’ve created a test document to show the basic table setup - and a column where I’d like the calculation.

I’m wondering if perhaps my overall data schema is not great for this?!

Thanks for any help!

I’m essentially tring to use the currency lookup and the date to apply a VERY simple formula to the amount in the first column… and have it calculate in the circled column.

However, due to the setup of the exchange rate table, I’ve had LOADS of trouble figuring this one out. And I’m guessing its VERY easy???!!!

@Brendan_Woithe

Until we get programmatic access to columns, you will likely want to use a SwitchIf() statement. (The red columns had data matching problems that I didn’t dig into further to understand.)

1 Like

Hello @Brendan_Woithe
Unfortunately with the way you set up your Exchange Rates Table you will need to use a switch() formula as @Ander showed.
Though there is another way, you can change the Exchage Rates table so it has just 3 columns: Date, Currency, Exchange.
image
That way you would only have to make a filter of the currency and the date to get the exchange rate with the next formula:

[New Exchange Rates].Filter(Currency=thisRow.Currency and Date<=thisRow.[Date of ER]).Last().Exchange

With this you can get any currency you add without having to change the formula, contrary to the switch formula.

2 Likes

Dear @Saul_Garcia,

I really respect people that are able to find different approaches to reach the goal.
Thanks a lot for the :eyes: opener :trophy:

It would be great if you could give access to your sample, thanks in advance :handshake:

No problem! :grin:

Did Coda team changed something? I used to click embed and it automatically added the Anyone with a link Can View Permission :man_shrugging:

Dear @Saul_Garcia,

Thanks, earlier today I faced the same issue with a supporting doc, that I later on had to update the sharing settings.

@Saul_Garcia @Jean_Pierre_Traets

I discussed this with them a few days ago. They changed it for security reasons. The Share/Embed modal needs better signage to reflect this change, imho.

2 Likes

Thanks guys!
I had setup my exchange rate table the way you see it due to the way I need to setup my google sheet to automatically grab the data. (Ie, 1 row per day - I’ve made a little script to just add a row to the google sheet once every day)

I can def see that its possible to re-work it to grab the data using the new schema, although its a little more complex than the current method of just adding a new row to the document at a certain time every day, and then 5 mins later sending that same data straight to coda using zapier. It also might interfere with some of the ideas I’ve had to clean up the data when the google finance formula fails (as it does once per week like clockwork with the USD rates on sundays!)

Appreciate the help. I’m going to have a fiddle and will report back. Cheers!

1 Like

@Saul_Garcia and others - just chiming in again to say a big thanks for this… and to let you know what I’ve done.
I realised pretty quickly that formatting my table as suggested would also break graphs I had created… so instead of trying to redo everything, I wondered if I might be able to solve the issue by running two tables for the exchange rates.
The first is the one I have - and it grabs the data from the google sheet.
The second is using the schema suggested. And it is created using automations based on looking at when the first table has a row changed. Its like automations were built for this use case. No stress on the timing (which is frustratingly slow for other applications) and extremely easy to setup.

I went down this path when I realised that building a lookup talbe with the new scheme probably wouldn’t be viable! :slight_smile:

Anyway - seems to be working - at least in my small test just now. I’ll build it out and hopefully in a bit update the entire app to use this table going forward.

1 Like