Back to basics: Excel and a VLOOKUP

The whole reason I began to dive deeper into analytics is that I thought excel could only take me so far. And I was right. Over the past year or so, I've learned that there are many things that a scripting language like Python with libraries like Pandas, Numpy, Matplotlib, SciKit Learn, and the likes can do that excel can't. Even a tool like Tableau makes things easier. For quick, basic analysis though - Excel is pretty solid and user friendly.

I'm sure the majority of adults who've spent any amount of time on a laptop are familiar with MS Excel and the basics of the ribbon we're all so familiar with:

In comparison with other tools I've worked with, Excel is really easy to use with the GUI interface. On a recent project, I was working with some international financial information. It was formatted differently since it came from different sources. Thanks to data cleaning skills like I used in my post: back to cleaning, I was able to get it in a simplified format in a simple data frame. The other problem: the values were in different currencies. I needed to get them in one currency: USD.

I found an article here on how to build a function to convert it using current exchange rates:

https://data-flair.training/blogs/currency-converter-python/

Even applying .lookup() in panadas could do the trick.

df['Amount_in_USD'] = df['Amount'] * df.lookup(df.index, df['Currency'])

But, since I was going to a Tableau platform anyway, and .csv or excel would work to get it there. Honestly, I wanted to practice a technique I'd used a long time ago that could expedite the process: VLOOKUP.

To get in excel from Python, just save the file as a .csv:

#saving file to .csv with filepath
df.to_csv(r'\financials\currency.csv')

A VLOOKUP basically looks through data to find matches in a given dataset by row. I can't use the data I had for the project, but will use a more simplified for this example:

After I'd gotten my file into excel and formatted it, this a simplified version of what it looked like:

My goal was to convert all of this to USD. I used the exchange rates from https://xe.com/currencyconverter/ on 03/29/2021 to get this information, again very simplified and you can get them to dynamically load with a web query - but for the sake of this exercise, I'll display only what we'd use for our table we'll use to 'look-up' the rates:

These are the exchange rates on the next sheet over in cells A2:B5.

For those of you who don't know, cells in excel traditionally use alpha characters for horizontal tracking and numeric for vertical. So this would mean the cell in the first column from the left, the second row down, and the cell in the second column, fifth row down respectively.

Back to our original table and creating a new column that we need to fill in:

In cell C2, we'll need to use a function that takes three arguments:

1) What you want to look up - in this case, the Currency Symbol (USD, EUR, etc)

2) Where you want to look for it. This range can be on the same sheet or located elsewhere.

3) If we want an approximate match or not (TRUE/FALSE or 0/1)

so it looks like this:

=VLOOKUP(B2,Currency!A:B,2,FALSE)

In this case, the range from the Currency tab, Cells A:B2 would return the value located in that cell wich is the exchange rate - remember these values?

This is great since to calculate the USD value, we'd just use the value returned from the VLOOKUP and multiply it by the amount located in the A column so the revised formula looks like this:

=A2*VLOOKUP(B2,Currency!A:B,2,FALSE)

Ah, perfection. We can upload this back into our workflow.

There are other ways to convert currency even in excel but this was the first one to come to mind and know there are a lot of other applications for this so I thought I'd revisit and share.

Basic VLOOKUP documentation can be found here: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

Next week I'll be diving back into Tableau - just for some feel-goodness. Stay tuned!