Scraping Data from tables in multiple webpages

[heiyan]

Hello Everyone,

I'm new to the forum and also very new to Excel VBA. I'm trying to scrape some data from a financial website:

http://www.aastocks.com/en/Stock/Com...4&symbol=00238

I would like to pull the data from the table with all the relevant financials (nested tables?), and ideally I would like to automate the process so that I can pull data from different tabs ("financial ratio", "profit loss", etc.) of the same stock ("Type=1", "Type=2", etc. in the url) of different stocks ("symbol=0001", "symbol=0002", etc. in the url)

Since I'm new to VBA, I tried to start with recording a macro of the actions of getting the table data via the Data->Get Data From Web function. However, the tables I want seem to be nested and I cannot select it and import cleanly. I have Excel 2013, so I tried recording a macro of pulling the data via "Data Explorer", but seems like nothing got recorded.

If anyone can kindly shed some light as to how I should proceed with this problem, that would be super awesome!

Thank you very much!!

[ducky_yeng]

Hello heiyan

I recently was looking into automation too. I found some really good posts in this forum if you search for "scrape". Some of the gurus/contributors have used QueryTables and MSXML2.XMLHTTP. I suggest that you take a look at those posts.
I have given it a go using QueryTables as I was just learning about them and provide my basic code as an example. It extracts data tables from the webpages and pastes it into an Excel spreadsheet. It is not the most elegant bit of code. Note that code was tried and tested on Excel 2010. I will leave it up to you to decide on how you prefer to implement it.

Hope that helps.

Please Login or Register  to view this content.

[heiyan]

Hello ducky_yeng,

Thank you so much for your response! I will certainly use your codes as reference as I dig deeper into the VBA world. I have to admit though, I have gone another route and successfully used R to automate the retrieval of the data, currently at the stage of cleaning up the table format. I'm still using Excel to help me in certain tasks, but R pretty much took care of the scraping itself. If you ever want to look into the possibility of using R, don't hesitate to let me know!

Thank you so much once again!!