How can I use VBA Code to Scrape the Prime Rate?

[mecerrato]

Hi I am not an expert at all but do some light VBA and mostly from asking questions and using bits and pieces from sites like stackoverflow, thank you all for posting these answers publicly

I have an excel sheet that I must update the Prime rate daily, I usually get it from the bankrate site: Wall Street Prime Rate | WSJ Current Prime Rate Index

I honestly just learned the term scrape and was wondering if anyone has some code I can use to accomplish this and place in the a button or the worksheet activate event?

 

[alansidman]

I would use power query/get and transform data and found on the data tab of the ribbon.
Here is the Mcode

let
    Source = Web.Page(Web.Contents("https://www.bankrate.com/rates/interest-rates/wall-street-prime-rate/")),
    Data0 = Source{0}[Data]
in
    Data0

which returns

Column1	This week	Month ago	Year ago
WSJ Prime Rate	4.75	4.75	3.25

 

[mecerrato]

I would use power query/get and transform data and found on the data tab of the ribbon.
Here is the Mcode

let
    Source = Web.Page(Web.Contents("https://www.bankrate.com/rates/interest-rates/wall-street-prime-rate/")),
    Data0 = Source{0}[Data]
in
    Data0

which returns

Column1	This week	Month ago	Year ago
WSJ Prime Rate	4.75	4.75	3.25

Do I create a sub routine with this code alone?

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.