Category Archives: Web Scraping

Website Scraping – VBA Code To Import Data From Website To Excel

I am going to show you 2 ways of scraping data from a website. One with the standard approach and 1 with JSON.

For this example, I want the stock numbers from yahoo in my worksheet.

The site is https://finance.yahoo.com/quote/%5EGSPC/history?period1=1520575200&period2=1552111200&interval=1d&filter=history&frequency=1d

This data is rendered in a table format so I can use the following code to bring it back to my worksheet:

This method worked buy was very clunky and slow.

One of the big things with web scraping involves getting the tables and rows in the table, and then returning the items back to your sheet, and that’s what the example above does.

This slow and sketchy method wouldn’t work for me. So I needed another way.

The data I want is really just a JSON string.

JSON supposedly replaced XML as a standard way of transferring data.

Anyway that’s fine, but Yahoo is speedily rendering stock quotes via a JSON string.

So what we are looking for is the data after the “HistoricalPriceStore” text. If you want to know if you are dealing with a proper JSON string, paste your string into http://json.parser.online.fr/

So, yes, that assessment is correct. So you can use the following faster code to scrape the information:

Your end product should look like this:

(no, I didn’t author all of this code, I got the ideas from various places on the internet, and put all the “pieces” together 🙂 . So kudos to all those wonderful people out there, which share their knowledge with us. Thanks!)

Let me know if you have any questions.

****************************************************

Facebooktwittergoogle_plusredditpinterestlinkedinmail
READ MORE