While Excel is not a programming language, it is one of those awesome tools that every person should be familiar with. You would be amazed at how many things can be achieved with it, even when on the surface it is just a simple spreadsheet. Just ask this guy :-).
Anyway, let’s get back to what I want to share today. I manage my expenses using excel, and I have some credit cards for internet purchases that charge in a different currency than the one in which I do my account consolidations. Getting the exchange rate for each transaction was out of the question, so there had to be another option.
Enter the magic of Excel’s STOCKHISTORY function. While the function is built for stocks, we can use it to get exchange rates using the <source-currency>:<target-currency> as the name of the stock. For example, to get the exchange rate from USD to EUR, we use “USD:EUR”.
In its simplest form, the function receives two arguments – the stock and a date – and creates an array containing the matching values for the stock on the date. For example, to get the USD:EUR exchange rate for 2024-01-01, we enter the formula:
=STOCKHISTORY("USD:EUR", DATE(2024,1,1))
Which adds this data to the worksheet:
| Date | Close |
| 01/01/2024 | € 0.91 |
Good start. For my purpose, I want just one cell containing the exchange rate from the currency of the transaction to my base currency based on the date of the transaction that is also in this row. We can also do that by changing other parameters of the STOCKHISTORY function that lets us tweak what data to fetch and how to return it. First, to get only the exchange rate, we set the Headers argument to 0, which removes the headers:
=STOCKHISTORY("USD:EUR",DATE(2024,1,1),,,0)
Returning:
| 01/01/2024 | € 0.91 |
Almost there! I only want the value of the exchange rate, so we need a bit more tweaking. There are 5 “properties” that can be passed to the STOCKHISTORY function, which define what values it returns. Setting property0 to 1 will ensure that only the closing rate is returned:
=STOCKHISTORY("USD:EUR",DATE(2024,1,1),,,0,1)
Returning:
| € 0.91 |
Great… But what happens when we try to get the exchange rate in a day where the market is not open? Even if Excel does not allow it, I do purchase things on Sundays. In these cases, the function will return #VALUE. For example, if we try to fetch the USD:EUR exchange for 2024-01-06, this is what we get:
What we can do here is fetch a range of dates for the exchange rate (from a start to an end date) and get the maximum from this range. Let’s do this in our example, fetching a range of 5 days and getting the maximum value:
=MAX(STOCKHISTORY("USD:EUR",DATE(2024,1,6)-2,DATE(2024,1,6),,0,1))
Which returns:
| 0.9138 |
Excel is awesome 😎.
Be First to Comment