How to Coda with Exchange Rates?
Converting amounts into a single currency
I am going to show how you can relate products with a specific exchange rate and turn them into a single currency value. My use case concerns my credit card statements.
My services are paid in Euro and in USA Dollar and finally all is converted to Euro. On the Credit Card statements I find the exchange rate per product.
To make it work I note per invoice the currency the contract is made up for and the exchange rate as I find it on my credit card statement. Below how this looks like:
My next step is to relate the credit card statement to the invoices. I apply a lookup that permits for multiple values. In the item settings I create two filters. One that makes sure that I can only select not yet selected invoices via this formula (to understand this better, see this blog). The other filter makes sure that I only see invoices paid via Credit Card (to avoid that I see all invoices). Below how this looks in my set up.
Now we have per credit card statement the invoices and our main challenge starts here. I defined a few steps as you can see below:
Step 00 is simply to show that we deal with various currencies. The real work starts with the rather simple steps 01 and 02:
thisRow.services.[Exchange Rate]thisRow.services.Amount
Once we have this information per product (the price & the exchange rate) we combine it. That is what we do in step 03:
Once you have a list of lists in your cell you can only calculate with each item by applying a virtual index. Using sequence we create the index and via FormulaMap() we run over each item via Nth(CurrentValue) . We divide the amount by the exchange rate to get the Euro value. In step 04 the Sum() function shows the monthly total. In the Delta column I relate this information to the amount I found in the credit card statement. For this month Feb I have lack 11.99 Euro, this means I have to check which invoice(s are) is missing. The other months are fine.
Using this tool I see in minutes what the state of affairs is. This should result in less questions and a faster reporting. I wonder why I did not make this already a year ago. The honest answer is that it took me some time to see the many options Coda offers.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.
