Excel Web Scraping Custom Function Builder

Build a Smart Combo Box

The clients request was simple I want to pull the following financial statistics from the Wall Street Journal and Market Watch:
1) Name form Ticker Symbol
2) Total Revenues
3) Non Operating Interest Income
4) Total liabilities

He was nice enough to provided the URLS as follows:

The Wall street Journal
https://www.wsj.com/market-data/quotes/AAPL/financials/annual/balance-sheet

Market Watch
https://www.marketwatch.com/investing/stock/AAPL/financials/balance-sheet/quarter”

I did the job and built him a couple of custom functions but then got to thinking that it would be better to build a tool that automatically creates the custom function.  This is what I came up with:

Excel Web Scraping Custom Function Builder

The video above give the basic idea of how the Excel Web Scraping Custom Function Builder works but without creating a hour video I could get in all the details
1) The textbox URL  is the wesite you want to scrape from. The assumption is that the target website has a variable like a stock ticker or a date that changes depending upon what you want to look up. You will note In this case I have changed AAPL to Var1 as thats the part that changes depending on what ticker symbol you are looking up

2)The textBox Var1 is the ticker symbol we are using to build the function.
3) The textbox Var Name is what will appear within the Parenthesis in the function =sEPS(sTicker)  in this case sTicker whish stands for s (string) Ticker (Stock symbol)

Uniquue Leads and Unique Follow
4) The tool uses specific text to identify where we expect to find the data you want to scrape.  So, in the example I used in the video for earnngs per share the leads were 1st “>EPS (TTM)“> which was unique text that brought us close the “EPS data we wanted”
5) The 2nd lead “”</span></div>” brought us right before the $3.29 EPS.  It didnt have to be unique because it is the first time it appears after the first lead  in  #4″>
6) The Textbox Unique follow appears directly after the EOS data so it tells what postion the EPS data stops
7) Function Name apears before the Parenthesis
8) the textbox Inner Text is what we want the Auto feature to look up and on the website in the case of the exampl Apple EPS it was  “3.29.”

You can download the tool here: 
We ask for your email address so we can send you any updates to the code.  We do not ever share emails addresses and take privacy very seriously.

Thanks,
Ray Mills

 

Webscraping Function Builder Tool

If you were able to utilize what you learned here or have suggestions to improve the code, 
or make suggestions about how better to improve it please leave a constructive comment Here

If you enjoyed this post, used the tool to great effect or found it helpful andl would like to say hello and buy me a cup of coffee Click here

Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me
Tagged , , . Bookmark the permalink.

Comments are closed.