I forgot where I heard this, but on some or another podcast it was declared that “Microsoft Excel is the gateway drug to programming.” And I’m coming to believe this more and more.
I’m someone with more than a passing interest in programming who simultaneously lacks any serious proficiency with writing code. Irony of ironies, right? Also, I’m now in this weird liminal space between being really good at manipulating data in a spreadsheet-type environment and seeing the possibility of what I can do with a real programming language while still sorely lacking in the practical know-how to get a lot of things done.
And that’s why I’m still tied to using Excel and Google Sheets for a lot of my data analysis work. Both are fine tools, albeit with their own quirks and limitations. Although Excel is good for the kind of work I do, which typically involves big CSVs that Google Sheets kind of chokes on, I strongly prefer Google Sheets for its more expressive functions and easy connectivity to outside data stores, including Google’s own services.
It’s for this reason that I’m going to be covering the subject of creating abstract, auto-generating formulas in Google Sheets only. Also, I’m like 90% sure one can’t use CONCATENATE() in Excel for the use case I’m presenting here, but I’d love to be proven wrong.
Part of my job is to analyze venture capital data, and oftentimes the scope of my analysis expands beyond US borders. Accordingly, I often have to deal multiple currencies, which can be a pain.
So imagine a column containing data for an arbitrarily large number of VC deals priced in an arbitrary number of different international currencies. (For the sake of this example, let’s assume these are all fairly recent deals, so historically accurate currency conversion figures aren’t important.)
Remembering that our dataset can be arbitrarily large, what’s the easiest way to convert those foreign currencies into USD so we can make comparisons on equal terms?
An example dataset
Below, to avoid using proprietary data, I fabricated an array of 20 sample deals using a random number generator and multiplying its output by different scalars depending on the round type. The size of deals do not necessarily correspond to any real-world averages and are here for demonstration purposes only.
Also, I picked 20 rows because it’s small enough to fit in a screenshot. For a sample size of 20 rows, it’s still easy to do all the conversions by hand sorting, but the ideal solution would scale to sheets with hundreds or thousands of rows and a huge combination of currency conversions. This is why we’re going to emphasize abstraction here.
Here’s what we’ve got to work with…
(Yes, the numbers are hideous. Deal with it.)
Here, we have sample deals from five different countries: the USA, Canada, the UK, France, and Germany. (I intentionally picked two countries that use the same currency for reasons that will become apparent later)
Converting foreign currencies to USD, some methods of varying efficiency
We have several choices for how we want to make the conversions of foreign currencies to USD. Here, I’ll share three ways, with each successive option being more abstract and scalable than the last.
To remind ourselves of what we want to convert our currencies into here, we’ll add a “Target Currency” column and set all values in that column to USD. And we’ll also add a “Conversion Rate” column where we’ll set our conversion ratio. (This column isn’t absolutely necessary, strictly speaking, but it keeps things organized and clear from a visual perspective.)
Let’s say you’re a masochist with a fetish for tedium and frustration. This is the method for you.
Remember, I said “masochist” and not “primitive cave dweller” here, so I’m going to run on the assumption that, being an enlightened user of spreadsheeting tools, you understand how to sort columns.
So we start by sorting the “Base Currency” column to make things at least somewhat easier to deal with… Note how all the base currencies are now grouped.
Now for the brute force part. We search Google for each conversion pair (“CAD USD,” “EUR USD,” etc.), copy and paste the ratio into our spreadsheet, and manually fill down.
Brute Force Conversion Table Making
For this small sample set, it took me almost exactly a minute to build a conversion column using brute force. But this is only for a small handful of currency pairs. If I was dealing with dozens of pairs, this would have been a much bigger task.
Enter The Joys of GOOGLEFINANCE()
Like I may have mentioned earlier, one of the nice parts about Google Sheets is that it gives users direct access to some of Google’s services directly through a series of bespoke functions.
For example, the GOOGLETRANSLATE() function lets users translate strings in spreadsheets from one language to another using the Google Translate engine. In conjunction with DETECTLANGUAGE(), one can generate some interesting formulas.
But here we’re going to talk about the joys of the GOOGLEFINANCE() function.
There’s standard syntax for pulling current and historical stock market prices:
GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])
So, if I wanted to find the closing price of Apple on March 7, 2014, I’d write the formula like this,
And Google automatically returns that information and builds a table to display it.
It turns out that the GOOGLEFINANCE() function also lets you find the current conversion rate between two currencies. (Unfortunately, at time of writing, it doesn’t let you find historical conversion rates.)
Here’s that syntax:
Using the official, three-character currency codes, we’d convert Canadian Dollars to USD using this function:
Now, for our table of VC deals we want to convert into USD, we could hard-code the currencies we want to convert into our formulas by group.
Here are the other hard-coded formulae:
Google Finance will return the current conversion rate and embed it as a number in the spreadsheet.
Below is a screenshot of our Google Sheet with an escaped version of the formula next to the conversion rate. (Note what’s in the formula bar for the selected USD -> CAD conversion.
And, once we’ve hardcoded all the formulas, all one would have to do is drag the corner of the cells down to fill in the rest of the table with the appropriate values.
Just like the original “brute force method” this is all well and good if the number of currency pairs is small, there are few rows, and any re-sorting of the rows will be on a whole-sheet basis.
Now, on to the fun part…
Dynamically generating currency conversion formulae using GOOGLEFINANCE(CONCATENATE())
Alright, so we start with the general form of a currency conversion rate query in Google Sheets, the same as what was previously listed.
Now, let’s look at our table and its columns.
- Column C: “Base Currency”
- Column D: “Target Currency”
Hmmm… seems familiar… If only there was a way to join those elements into a formula.
Enter Google Sheets’s CONCATENATE() function, which is similar to its cousin CONCAT(), except for the important fact that it lets you concatenate an arbitrary number of elements.
Importantly, in Google Sheets, the output of the CONCATENATE() function is not simply treated as a string. Its output is usable inside another function.
So, to start, let’s concatenate cells D2 and E2 using the following formula:
And as we can see, the result is “CADUSD” which is is the required pairing of source and target currencies we need in our formula.
Now that we’ve proven we can build our one part of our formula using CONCATENATE(), let’s see if we can build the rest of it.
Typing in the following formula produces outputs what we need to give to GOOGLEFINANCE()…
So, now we can bring it all together…
Before showing the nice gif of how it all “just works,” give me one second to explain how one formula will be able to generate a conversion ratio for all of the currencies in our set.
The D2 and E2 cell references are relative, which means that as I drag the equation down the sheet it will still pull from the cells in the two adjacent columns, but will take the value from each row. So that same equation, if applied to, say, row 3, would read from D3 and E3. From row 4, it would pull from D4 and E4, and so on down the sheet.
So, here’s the moment you’ve been waiting for…
At this point, it’s just a matter of multiplying the “Deal Amount” column by the “Conversion Ratio” column to generate the “Converted Amount.”
And there you have it…
One of the features of this kind of query with GOOGLEFINANCE() is that it dynamically updates as new currency exchange rate data becomes available. This can be viewed as either a bug or a feature, depending on your particular needs.
In the event you don’t want these numbers to change, simply copy the contents of the columns that update dynamically and paste those cells in “as values”. (In Google Sheets, goto Edit -> Paste Special -> Paste Values Only [⌘ + Shift + V].) As this suggests, it just pastes in the alphanumeric values of the cells, and strips out the formula data.
In the event that you still want to edit the formulas, which in variably you will, my suggestion is to duplicate the tab so you have one dynamic, editable version, and the static version you can work off of in later analysis.
That’s All, Folks!
This has been a somewhat protracted way of saying that a little bit of abstraction makes for a time-saving and viscerally satisfying (at least from my perspective) data analysis experience.
CONCATENATE() can be used outside or inside other functions in Google Sheets to build flexible, extensible auto-generating formulas in your spreadsheets, and I’m very much looking forward to a exploring how else it can be applied in the work that I do.
This is also my first time offering up one of these tutorials, so if you liked it and want me to make more of these, please let me know! Future topics I want to cover include pivot tables, demonstrating a superior alternative to VLOOKUP() and INDEX(MATCH()), and a couple of others.