Because Internet Explorer automation and data scraping with Excel is a topic that I like very much, I played with a Workbook in order to import Exchange Rates from a website considering the parameters input in the excel file (currency, start date and end date). For more fun, we also need to create a dynamic chart which will show the evolution in the given time interval of the selected Exchange rates. At the end of the post you can find the file for free download.
Steps to complete this task:
A) We want to take the values from this link: Exchange Rates Website, so we can start by inspecting the elements from the page using the “Inspect Element” browser feature:
We find out this information:
B) Then, we create the input parameters in Worksheet “Exchange_Rates”, cells “C3:C5”:
C) The next step is to write the macro which will import the data for the selected input values into columns “D:E”, when we press the “Import Data” button. This is the most interesting part and after some research the macro that we wrote is the following:
D) After we have completed the import of desired data we can start creating the dynamic chart. For this we first create some named ranges. Go to “Formulas”, then “Name Manager”, “New” and create the following two named ranges:
Explanation: The Counta function counts the number of not empty cells into a range and the Offset function creates the named range starting from cell D/E8 until the last used cell in every column.
Now input the named ranges as data source for the chart:
For the Chart Title we can create a formula in Sheet “Lists” in order to have it dynamically changed considering the selected currency, the starting date and the end date of the imported data:
* at the end of this post you will find also a video where I explained detailed how this formula works.
Now the chart is automatically refreshed after new data is imported no matter how many rows of data we have:
I also created two videos to complete this post, you can watch them below (I am not the best presenter and I need to work more at this, so please don’t judge too hard the quality of those first two videos. Maybe in the future I will create better ones) :
- Presentation of the file and how it works + some formulas for dates:
- How the dynamic chart title formula works:
UPDATE 16.06.2017: Website changed data format. Download: New updated file