I've said it before, and I'll say it again: Life is hard enough; we really don't have to make it any more difficult. If there is an easy solution to a problem, you can bet I will jump right on it. This time I came across the problem with fetching currency data in spreadsheets.
Let me start with my main problem: I have a MacBook. I was researching ways to get currency data into Excel and had to admit defeat at some point. There was just no simple solution to my problem for Mac. If you have Windows, this isn't a problem. I could explain what to do within four steps or so (and maybe I will; stay tuned for another article).
You see, the problem was that Excel for macOS does not offer the "Data from Web" feature. So I had two options, get another device or switch to Google Sheets.
I decided to do the latter and here is what I found out.
The two methods to get currency data in Google Sheets
There are many methods to get currency data into Google Sheets. However, some involve more steps than others. Here is a short overview of the two fastest and easiest ways that worked perfectly for me.
- GOOGLEFINANCE: simple function, easy and fast, suitable for small data sets
- Currency API: great if you have an API key, quick and easy, ideal for big data sets
Here is a quick step-by-step to show you the two methods.
GOOGLEFINANCE
This one is pretty straightforward. You can use a formula, and Gsheets will fetch currency data from Google. You only need to know the international symbols for the currency pair you want to convert. For US Dollar, this is USD; for Euro, it's EUR; for the British Pound, it's GBP, and so on. If you know them and the formula, you are good to go.
The formula goes as follows:
=GOOGLEFINANCE(“CURRENCY:<base currency><target currency>”)
If we put this into practice, our base currency can be USD and our target currency GBP. Our formula will therefore look like this:
=GOOGLEFINANCE(“CURRENCY:USDGBP”)
And that's it. If you are looking for a more detailed tutorial, this might help. You can find a step-by-step guide for both methods, including screenshots there.
Currency API
The previous method is the easiest. However, I soon realized that it doesn't work as well for larger data sets. This is where method two comes in.
Just a heads up right away: you will need an API key and an add-on for this one. Don't worry about the API key; many providers offer free plans. I used currencyapi.com's free plan for this method. The add-on is free as well and is installed within seconds.
Here is how you do it:
- Get the API key: After creating your account, you will have your key on your dashboard. Your key should look something like this:
- Install the add-on: Next, we need a connector that allows Gsheets to connect to our API. I go to Extensions > Add-ons > Get add-ons, search for "API Connector," and install it.
- Create a request: To do that, I open the connector, which I can find under Extensions. I then click on "Add new request," enter my API key in the API URL field and hit Save and then Run. You can name your request as well if you want to.
- Transposing it: What I got out was not pretty to look at, so I decided to transpose the table and make it easier to read. So, I opened up another sheet in the same file and entered
=transpose(<sheet-name>!1:2), whereby you, of course, have to change <sheet-name> to the name your sheet has. For me, it was simply "sheet1". So I typed=transpose(Sheet1!1:2)
More than one way to skin a cat
There are more than just these two ways to get currency data into Google Sheets. I, however, found these to be the fastest and easiest ways to do it. You don't need any special programming or spreadsheet skills to get the result you are looking for.
I hope this article helps. Let me know what you think. Did you find an even easier way?
I hope to do more articles on saving time or hacking complicated work situations in the future.