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
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:
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.”
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
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
