Currency conversion in Google Docs Spreadsheet

One of the complexities of working overseas is keeping a budget. I get paid in two different currencies (Qatari Riyals and Canadian Dollars) and due to the infrastructure in Qatar I have to work with cash for most of our everyday transactions.

While working with my budget spreadsheet on Google Docs, I spotted this cool little formula that has allowed me to seamlessly convert back and forth between Canadian Dollars and Qatari Riyals.

Here’s the formula

=CanadianAmount*(GoogleFinance(“CURRENCY:”CADQAR ;¬†“average”))

In practice here’s how it looks.

I put the two variables QAR and CAD in the spreadsheet itself and call them via &TRIM(). ¬†This allows me to reverse the CADQAR to QARCAD variables easily. The “average” modifier gives you a good-enough approximation of the current exchange rate.

 

What’s cool with this automatic conversion is that I’ve figured out a way to enter an expense in either currency, and have it automatically reflected in an automatically updated dollar amount in both currencies. Spreadsheets FTW!

 

Here’s a spreadsheet that can help you figure this out on your own.

 

https://spreadsheets0.google.com/spreadsheet/ccc?key=0AqhqY231XZd3cFBiY2VqeWdmNWdaMEhMazdtZGxvT1E&hl=en#gid=0