VBA Case Study #7 – Currency exchange rate macro

[IMAGE] Filter forex rates with VBA code

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

[Image] Download Workbook Download the sample file with VBA code
Currency-Web-Query (138 Kb)

#1 – Why did I create the Macro?

» Back to contents

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?

» Back to contents

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?

» Back to contents

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:

  1. Manually navigate to the webpage
  2. Select the periods and currencies you need
  3. Click on “Next”, wait for the database to load
  4. 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?

» Back to contents

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:

https://www.mnb.hu/arfolyam-tablazat?
devizaSelected=EUR&deviza=rbCustom&datefrom=2018.01.01.&
datetill=2019.12.31.&order=0&customdeviza%5B%5D=EUR&
customdeviza%5B%5D=USD

(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?

» Back to contents

Structure

The system is built using 2 separate macros:

  1. Sub ReplaceMonths()
  2. 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

» Back to contents

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

» Back to contents

[IMAGE] Daniel Lajosbanyai

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!

Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.