Excel VBA Case Study #7 – Currency exchange rate macro
Last updated on December 10, 2022 By Victor Chan
Summary: Are you an accountant or bookkeeper? If you have ever used Excel’s built-in web-query function then you’ll know it can be a great help.
In today’s case study I’m going to show you how web-query can transform your daily routine task of searching for currency exchange rates from a yawn-inducing process into an absolute cake-walk!
Paired with some event handling, the result is every accountant’s dream – a workbook that automatically updates and filters an exchange rate database!
Difficulty: Intermediate
You can download a copy of the full workbook with all the VBA code in it from this link:
Download the Sample Workbook
Download the sample file with VBA code
Currency-Web-Query (138 Kb)
#1 – Why did I create the Macro?
The work of accountants and controllers can often involve dealing with currency rates.
I know that from close experience, because my bride-to-be is also in the same situation. Her role requires her to check and book invoices using the correct daily currency rate at the exact date of invoicing. That’s not a big deal to do once, but it must be done again and again which can get tiresome.
The process is as follows:
- Check the date of the invoice
- Look up currency exchange rate on that date
- Book the invoice using the exact rate found
Easy, right? Of course, but VERY boring.
As her future groom, I felt excited to give her a little help by using my Excel VBA programming know-how 🙂
#2 – What does it do?
To automate the above process for my future bride, I wrote a tiny macro that does the boring part, and all she needs to do is choose the 1. Time period and the 2. Currency pair she is looking for.
There is a web query in the Excel workbook that connects to the currency database of the Central Bank of Hungary, and downloads the EUR/HUF and USD/HUF rates from 2018.01.01 to 2019.12.31. (HUF = Hungarian Forint)
I needed only these currency pairs – but the query can be modified to download more columns of currencies. As the currencies are stored in columns in the query, the Excel file can be extended with new currencies anytime.
The macro calculates the rate between USD and EUR with the help of their rate with the HUF – we can call it a “crossrate” – and it is reliable enough for our purposes.
The whole database is searched and refreshed automatically when the Excel file with the macro is opened – no hassle, just the joy of a well written VBA program!
#3 – How is it better than using Excel without VBA?
Currency rate databases can be found online, but you have to navigate to a trustworthy site, select the period and currency pair, and only after these steps can you finally reach the desired rate.
All these steps can be done faster if you use a macro like the one I programmed. Just open the file, wait 2-3 seconds, and you get all the latest rates for the whole period specified in the query.
Then, in the Excel file, you can easily select the Year / Period / Currency pair with drop-down lists on the sheet “Filter”.
This file and macro is like a special wrench in your toolbox: if you have a task, which needs a lot of manual work, you will definitely want to solve it with such tool. 😄
Can you imagine, how much time you can save with it?
You don’t need to:
- Manually navigate to the webpage
- Select the periods and currencies you need
- Click on “Next”, wait for the database to load
- Scroll down for the appropriate date
All this is done automatically in the macro, and you only need to do step 2 in the Excel file.
Then you will see the whole month’s rates, and you don’t need to scroll down a long webpage for the correct dates.
#4 – How to set up the Web Query?
The query is set up on sheet “EUR-USD”, in cell “A1”. It uses the inbuilt web-query function of Excel:
After clicking on the highlighted button, I had to type in the exact address (link) of the query I need.
This is as follows:
(Here’s the Google Translate version in English)
If you navigate to this link in your browser, you’ll get a screen like this:
Now, this is exactly what we would need to set up in Excel as well – that’s why we use the in-built web query function.
So, after selecting the web-query function, you will see a menu like this:
Copy the link in the “Address” field and click on “Go”. This menu works like an internet browser – you can navigate to any page. But it’s power is not in this browser-function, much more in the possibility to select databases to download.
When the link you copied has succesfully loaded, you have to click on the highlighted arrow symbol in the upper left corner of the database field to select it for the query. When selected, it will change from an arrow to a tick.
Now you just have to click on import,and then select where you want to place the query database in your Excel file. I selected cell “A1” on sheet “EUR-USD”.
The imported database will then look like this:
If you ever need to modify your query in the future, you can do like this…
Just select the cell where you’ve inserted the query in the import phase, and then this “Properties” button will become usable:
Click on Properties, and you’ll see the list of modifications available:
I circled in red the check box that allows Excel to refresh the database when opening the file. This is the most useful function I found in the web-query options. The other options were not relevant for this project.
#5 – How does the code work?
Structure
The system is built using 2 separate macros:
- Sub ReplaceMonths()
- Sub FilterMacro()
First, I will tell you about the macro “ReplaceMonths”, because it is related to web query.
After that we will go through the “FilterMacro” which does the interesting part: collecting the relevant info for the user from the database.
ReplaceMonths() macro
Why do we need this part of the code? Because the database downloaded with the web-query is in Hungarian format, and therefore it is not really English-friendly J The names of the months and their format are also incorrect.
So to make it easier for more people to user, I made some corrections to the data imported via web-query:
- replace the Hungarian month names with their English equivalents
- replace the commas (used as decimal separators in Hungary) with periods
The complete modification code is in Module1 of the VBA project:
Sub ReplaceMonths()
Sheets("EUR-USD").Select
Columns("A:A").Select
On Error Resume Next
result = Selection.Find(What:="április", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Err.Number <> 91 Then
Selection.Replace What:="január", Replacement:="January", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'... and so on for each month
Range("A:A").Replace What:="december", Replacement:="December", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
(Note: download the workbook here to see the rest of the code that translates the months from Feb to Dec to English).
What does this do?
Selects the sheet where the query is saved, and searches for the Hungarian version of April. (Why April, and not any other month? It is just so, no special reason behind it)
If at least one match is found, then it means that the query is containing Hungarian names, and the replacements should be done. Replacements will be done for all months, as you can see in the Excel file – but I did not copy here all of them. They are just copy-paste J
If there isn’t any match in column “A”, then the search will throw an error – number 91. In this case, no need for replacement and the macro will jump to the “End If” part.
Let’s see, what comes after:
'Pull down formula in Column "D" to the last row
Dim lrow As Long
lrow = Sheets("EUR-USD").Range("B65000").End(xlUp).Row
Range("D4").Select
Selection.AutoFill Destination:=Range("D4:D" & lrow)
When the month names are already correct, the formula for the EUR/USD cross rate has to be pulled down to the last row. For this purpose we need to define the last row, and copy the formula down until we reach it.
Last, but not least, we have to deal with the comma/period decimal separator problem:
'Replace commas with periods
Range("B:C").Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Filter").Select
End Sub
This is the same replacement function we used for the months, just the parameters are changed.
When all the needed modifications are done on sheet, the macro will select the sheet “Filter”.
FilterMacro()
This macro is found in the VBA Object “Sheet3 (Filter)”. I had to write it here – instead of in its own code module – because it is driven by VBA events that are specific to Shee3.
As all programmers should, I started with declaring the variables:
Sub FilterMacro()
'creating variables
Dim lrow, lrowFilter, frow As Long
Dim cell, rngDate As Range
Dim period, year As String
Dim ws As Worksheet
Dim result As String
Dim ColCurrency As Long
After declaring the needed variables, the sheet has to be cleaned, to get rid of the old and not relevant rates:
'delete old list
Sheets("Filter").Range("C2:D1000").Value = ""
Next the periods need to be set. They are taken from sheet “Filter”:
'define period and year
period = Sheets("Filter").Range("B3").Value
year = Sheets("Filter").Range("B2").Value
'define worksheet
Set ws = Sheets("EUR-USD")
To exactly define the working range for the macro, we need to set the first and last rows of the desired range. Only after that can we give the command to define the range:
'define first and last rows
frow = 4
lrowFilter = Sheets("Filter").Range("B65000").End(xlUp).Row
lrow = Sheets("EUR-USD").Range("B65000").End(xlUp).Row
'define ranges
Set rngDate = Sheets("EUR-USD").Range(Sheets("EUR-USD").Cells(frow, "A"), _
Sheets("EUR-USD").Cells(lrow, "A"))
When the range of the rates are set, we have to tell the macro in which column it can find the selected currency rate. I did this with a simple If-Then-Elseif function:
'select currency pairs
If Sheets("Filter").Range("B6").Value = "USD/HUF" Then
ColCurrency = 3
ElseIf Sheets("Filter").Range("B6").Value = "EUR/HUF" Then
ColCurrency = 2
ElseIf Sheets("Filter").Range("B6").Value = "USD/EUR" Then
ColCurrency = 4
End If
OK – all of the important preparations are done, let’s see the real action!
The below cycle compares the query elements on sheet “EUR-USD” with the selected year and month strings, and if there are any matches, then copies it on sheet “Filter”
'Cycle
For Each cell In rngDate
'Condition
If cell.Value Like year & "*" & period & "*" Then
lrowFilter = Sheets("Filter").Range("C65000").End(xlUp).Row
Sheets("Filter").Cells(lrowFilter + 1, "C").Value = _
ws.Cells(cell.Row, cell.Column).Value
Sheets("Filter").Cells(lrowFilter + 1, "D") = _
ws.Cells(cell.Row, ColCurrency).Value
End If
Next cell
Before the macro is ended, there is one more thing to do – handle the errors! If the macro is run, and there aren’t any matches, then there is no such data in the query. We will be warned to select another period:
'Error handling
If Sheets("Filter").Range("C2").Value = "" Then
MsgBox "No data. Please select another period!"
End If
Sheets("Filter").Select
End Sub
That’s it!
VBA Events
As I told you in the previous section on the Sub Filtermacro, we use VBA events to make the Excel file more user-friendly. Just above the Sub FilterMacro() you can see the event handler code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Filter").Select
If Not Intersect(Target, Target.Worksheet.Range("B2")) Is Nothing Then
Call FilterMacro
End If
If Not Intersect(Target, Target.Worksheet.Range("B3")) Is Nothing Then
Call FilterMacro
End If
If Not Intersect(Target, Target.Worksheet.Range("B6")) Is Nothing Then
Call FilterMacro
End If
End Sub
The explanation of these lines is simple: whenever the user changes the year, month or currency on sheet “Filter”, the macro will run automatically.
No need to click on a button to start the macro, or to select it from the macros list – just change one parameter in the drop-down list, and it refreshes in the blink of an eye!
There is another similar event handler in sheet “EUR-USD”. This will start the ReplaceMonths() macro if the query database is updated, and cell “A4” has changed:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("EUR-USD").Select
'Replace Hungarian months with English month names on startup
If Not Intersect(Target, Target.Worksheet.Range("A4")) Is Nothing Then
Application.Run "Module1.ReplaceMonths"
End If
End Sub
That’s all the code you will find in this file!
Feel free to modify it to suit your needs, and don’t be afraid to practice coding!
#6 – Summary of key learning points
The idea behind this workbook was to make the life of an accountant easier.
With the use of Excel’s inbuilt Web-Query function it can be done!
Someone may call me lazy, because I don’t want to do this manually, but hey! Lazy people can help to push progress in the world by finding easier ways to do things!
With the query I already had a nice database in Excel, I just needed it to be able to filter it fast and easy. That is why I love the short and simple “For each” cycle in the Sub “FilterMacro”. It does the job!
Plus we used event handlers to make the macro even easier to use. They are your friend, make sure to use them in situations like this!
#7 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago. Now I enjoy writing macros for every task I can automate and feel reall
happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
Related Posts:
- Excel VBA Case Study #1 – Sheet Saver and Sender Macro (18 steps in 10 seconds)
- Excel VBA Case Study #2 – Map Mouseover
- Excel VBA Case Study #3 – File Checker Macro
- Excel VBA Case Study #4 – Invoice Generator Macro
- Excel VBA Case Study #5 – Making a User-Friendly Invoice Generator with Input Forms
- Excel VBA Case Study #6 – Displaying Images in Excel using VBA User Forms
Author: Victor Chan
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.