Hey there!
I’ve got freelancers who are paid in different currencies and I’d love to have the amounts they are requesting automatically converted from their currency into GBP based on live exchange rates.
Has anyone been able to figure this out? Or any ideas on how I might be able to achieve this?
Any and all thoughts are much appreciated!
Thank you!!
Dear @Ben_Swanson,
An important factor is the source of the daily exchange rate, in the past I have been working with a company that only accepted the day rates (fixing) from Oanda.
In that time Coda wasn’t existing, so I used Excel, I exchanged only 3 currencies and did the job only at the beginning of the next month
The most simple version (for sure free of charge):
Basically you could import these figures in and use them to exchange the currency to the GBP based on the transaction date. It’s also a little bit depending how many currencies and how often you need to calculate the exchange rates, that will influence if it’s a workable solution.
More advanced (costs could be involved) an automated version to be made that reads / updates the exchange rates in your coda doc.
Looking forward to see your input and maybe other members have something to add.
I have managed to implement a system within my coda docs for currency exchange.
We currently grab the currency data once a day. This seems to be accurate enough - well within tolerances required for our business.
It isn’t realtime. However, it could be done say every 30mins or so if needed.
Ours stores the data once a day though - as we calculate final amounts based on the day that we are paid - so need to be able to do conversions based on date.
Our method is fairly simple.
I have a google sheet that (using a tiny bit of programming) pulls in forex data once a day for the 6 currencies we need.
Then 15 mins later I get Zapier to bring that data into a table in coda.
Long story - possible “slowly”.
Realtime - I can see possible ways using zapier and googlesheets - close to realtime. Maybe a couple of mins. Perhaps depending on the accuracy needed you could pull the data every minute and just keep five mins or so of the data in coda and correct for the latency in the figures by keeping track of the times the automations occur etc.
This is great! Thank you both for your awesome input on this!
@Brendan_Woithe are you able to kindly share any more info on your setup with this? e.g. you mentioned a bit of coding on the Google Sheets side, what was required here? I thought Google sheets had its own currency conversion that you could pull in? Or is it that using your method is more accurate?
And perhaps more importantly, you mentioned that you’ve fixed it up in Coda so that it can handle when the data is missing there too, is there anything you could share on that side as well?
Thanks in advance for anything else you can share to help me get this set up, it’s much appreciated!!
Ben
Hi @Ben_Swanson! Sorry - I totally missed this.
So - I’ve streamlined my setup and its now working nicely with even less scripting. I’m no longer using a script with google docs - instead using Zapier. Mostly because I didn’t know what I was doing with the script… and it had a few bugs, and Zapier works perfectly.
Once a day zapier runs a zap which adds a line to my google sheet.
In it, it stores the date, I convert that to a pretty date (to make processing with coda / formatting the google finance formulas in google sheets) and then adds in the finance formulas in each column.
These finance formulas look like this :
=INDEX(GoogleFinance(“CURRENCY:” & “GBP” & “AUD”, “price”,“1/17/2020”), 2, 2)
So the date its getting from the zap, but the rest is hard coded. I just add a bunch of these for the different currencies I need.
It means the final doc ends up looking like this :
Then about an hour later, another zap runs that brings all that data into my coda doc.
A problem I had earlier was that google finance didn’t always get data straight away. For reasons I’m not sure about, it would just ignore US data on a sunday, but it would appear the next day.
Thankfully, this doesn’t impact me greatly - but I was able to fix this just using a coda automation. Once a day it would look for ANY blank entry in a row of the currencies table, and then trigger another zap that re-imports than entire row from the data in the spreadsheet.
Does that point you in the right direction?
Thanks Brendan, very helpful! I really appreciate you taking the time to write this up and for the clear instructions!! Hopefully this will help others too!
Hello everyone,
I’m new to Coda also looking to convert JPY to USD, as I purchase products from Japan to sell stateside.
I have a table with each product listing the cost in JPY and the conversion to USD. Is this possible with the formula provided by @Brendan_Woithe?
Currently in gogole sheets, I use this formula: (=C33*GOOGLEFINANCE(“CURRENCY:JPYUSD”)
Or would I just setup a daily zap using the formula I currently use?
Thanks for the help!
-Charles
I personally use a daily zap just to keep things accurate. That way you can also use a lookup for a specific date for the currency conversion.
Dear @Charles_Coates,
As @Brendan_Woithe, knows better then me, it seems to work with Zapier.
You might also consider to upvote a pack for it here
