Currency Converter Script
Currency exchange rates change all the time, so if you’re bidding on international markets you’re liable to make mistakes. This dashboard will help you to bid just as much as you intend to, from Chinese Yen to Angolan Kwanza, for example. Talk about being internationally minded!
What’s included?
How does the script work?
This Google Sheets dashboard has all the currency information you will ever need to take back to your PPC reporting. Use this sheet to help keep currency conversions simple and up-to-date.
You can use it for all sorts of things, such as:
1. Improve your analysis and compare performance across markets
When analyzing your business across markets, it’s difficult to compare cost-related metrics unless everything is the same currency.
You might also want to check out the volatility of a currency before moving into a new market, especially if you’re going to need to convert money to make payments – if the exchange rate changes by 10 percent over the course of a month, you may end up paying much more at the end of the month than you would have at the start.
2. Use for reporting and billing
Like many of our clients, you may do the majority of your business in the country in which you’re based but have branched out to do some international business, too. Whether your operations are digital or otherwise, it’s usually preferable to have these international ventures use the local currency. For AdWords, this means setting up a different account in a different currency and getting billed for it at the end of the month in this currency.
It also makes reporting a bit of a nightmare – if you’re advertising in the US, Canada, Mexico, the Bahamas and Bermuda, you have to manually look up the exchange rate for each of these each day just to know how much you’re spending. Using a currency conversion function makes this automatic, keeping the exchange rate current, so that you always know your total spend in US dollars.
3. Bespoke paid-for currency conversion software can be expensive
Currency conversion software can cost anything from $5 to $1,000 a month, depending on how heavy your usage is, how frequently you want rates to update and how flexible you want to be with the currencies you use. In general, the more recent and accurate you want your data to be, the more you are going to pay.
Back in 2011, Google had its own finance service that retrieved exchange rates (and other financial data) for free, making it easy to perform currency conversions in scripts. The service itself has now been deprecated, but a method to access this data lives on through the GOOGLEFINANCE function in Google Sheets.
How do I use it?
The sheet has four pages:
- The Current Exchange Rate sheet. This is a table of the current exchange rates between various currencies, updated by Google every three to 20 minutes. Feel free to add in any currencies you are interested in; you can look up their three-letter currency code here. If for some reason it’s not providing you with the exchange rate, it could be that GOOGLEFINANCE doesn’t support exchange rates for that currency.
- The Detailed Exchange Breakdown sheet. This is for looking more deeply into the exchange rate trends – how much has it changed over the last month? Over the last year? After choosing a lookback window, it gives you the exchange rates at close for each day, as well as graphing them and giving you the highest, lowest and average values in that time. If you want this detailed breakdown for several different currencies at once, you can duplicate the sheet and have a different conversion on each copy. To use, just enter a lookback window in days in cell B1 (or custom start and end dates in cells B2 and B3), then the three-letter code for the currency you’re converting FROM in B4, and TO in B5.
- The Exchange Rate To Single Currency sheet. This sheet gives you a slightly less detailed look at the exchange rate to a single currency. Alter any of the red boxes to get exchange rates from many different currencies to just one, with the current exchange rate alongside the average, maximum and minimum exchange rates for a given time period.
- The Useful Date Formulae sheet. A little bonus – this sheet contains some formulae for date periods you may want to use in the detailed exchange breakdown. These correspond to some of the date windows used in AdWords – useful if you’re in SEM and are using it to convert currencies.
If you’re more technically inclined and would like to implement currency conversion in code, here’s a JavaScript template to get you started:
