Bond rate tracking with Power Query

26. April 2016 Excel, Projects 0
Bond rate tracking with Power Query

I was always curious how I could get Power Query useful. In the same time I was struggling with tracking some bonds I was interested in, I'm a visual guy and I was not satisfied with the type of charts my bank could provide. So these 2 needs met and I have created an automated tool to track how some of the funds are moving against rolling averages (7 and 30 days) with the help of Power Query, Excel and a tiny bit of macros.

What can this tool do?

Once you open it Power Query automatically updates the background database for the funds I have selected. This part needed quite some data cleaning, so Power Query enabled me to scrape the site for the data and get it into the right shape as well. I'm also pretty happy that I could get the URL query right and figure out every attribute needed to get the right dates refreshed.

As soon as the data is downloaded you will be able to select the bond or the time period you would like to see. The periods always work backwards, for example: last three months from the newest data in the sheet.

Some macro was needed to make sure that all filters refresh automatically. I thought I could get this project done without adding macro, but I could not get it reliably working.

Before you download, please make sure you have the Power Query add-in installed on your version of Excel, otherwise it will not work.

 


Leave a Reply