Web Query -- Web Scraping Stock info from Thousands of Pages

arthur72j

New Member
Joined
Apr 2, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
HI,

I am attempting to pull data from MarketWatch, and am proving unsuccessful thus far, any help would be much appreciated.

I have a list of all Tickers on NASDAQ (over 3000 of them), and am attempting to scrape from using a table with a list of all URLs (="https://www.marketwatch.com/investing/stock/"&B2&"/analystestimates?mod=mw_quote_tab" -> where "&B2&" links to the ticker in that row [B2 is 'A']). I am trying to pull "High", "Low", and "Average" analyst estimates from the table within the webpages (uninterested in "Median" and "Current Price" -> I removed them when linking the webpage to the worksheet.

For individual, and a small number of rows, it works, but when trying to get it to work for even just a list of the S&P500 tickers (500 of them), and for the over 3000 rows I need it to work, it just comes up with with an error and tells me to quit Excel. I have attached photos of my worksheet, and hidden irrelevant columns.

I am working on Excel through a VM (Parallels) on an M1 Mac which I believed may have effected it, however I had a CS friend working on a Windows PC try it and it came out with the same error.

I apologise in advance if there was a thread for this, I couldn't find it.

I tried to upload more photos of the Power Query Editor, but it wouldn't let me.

I would greatly appreciate any help.

1617397363354.png
Screenshot 2021-04-02 at 21.36.19 copy.jpg
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Rather than try to re-invent the wheel, look at this link and see if this package does what you want. I use it to track about dozen stocks, but the author has indicated it can handle an unlimited number.

 
Upvote 0
Hello, can you indicate the complete link of the page and what you want to extract?
"AAPL | Apple Inc. Analyst Estimates | MarketWatch" for ticker AAPL, but it obviously needs to fill in automatically for all stocks, then "High", "Median", and "Low" within the 'stock price targets' table, leaving out "Average" and "Current Price". I have pasted below how it should look, I transposed the table to get it to look like this

HighLowAverage
$175.00$83.00$151.80
 
Upvote 0
Try this
VBA Code:
Sub marketwatch()
    Dim http As Object
    Dim html As Object
    Dim elements As Object
    Dim tr As Object
    Dim td As Object
    Dim r As Integer
    Dim c As Integer
    
    Set http = CreateObject("MSXML2.ServerXMLHTTP")
    Set html = CreateObject("htmlfile")
    
    With http
        .Open "GET", "https://www.marketwatch.com/investing/stock/AAPL/analystestimates?mod=mw_quote_tab"
        .send
        html.body.innerHTML = .responseText
    End With
    
    Set elements = html.getElementsByClassName("table value-pairs no-heading font--lato")(1)

    For Each tr In elements.getElementsByTagName("tr")
        r = r + 1: c = 1
        For Each td In tr.getElementsByTagName("td")
            Cells(r, c) = td.innerText
            c = c + 1
        Next td
    Next tr
    
End Sub
 
Upvote 0
Try this
VBA Code:
Sub marketwatch()
    Dim http As Object
    Dim html As Object
    Dim elements As Object
    Dim tr As Object
    Dim td As Object
    Dim r As Integer
    Dim c As Integer
   
    Set http = CreateObject("MSXML2.ServerXMLHTTP")
    Set html = CreateObject("htmlfile")
   
    With http
        .Open "GET", "https://www.marketwatch.com/investing/stock/AAPL/analystestimates?mod=mw_quote_tab"
        .send
        html.body.innerHTML = .responseText
    End With
   
    Set elements = html.getElementsByClassName("table value-pairs no-heading font--lato")(1)

    For Each tr In elements.getElementsByTagName("tr")
        r = r + 1: c = 1
        For Each td In tr.getElementsByTagName("td")
            Cells(r, c) = td.innerText
            c = c + 1
        Next td
    Next tr
   
End Sub

MrGes,

My VBA knowledge is slim (nought), however, from what I can gather, the CBA code searches the data for one URL (with the AAPL ticker), however I have a list of thousands of tickers for which I need to collect the data. I inputted the code into VBA (which I believe I did correctly) however there seems to be no change, not even for the AAPL ticker.

I have attached an XL2BB mini-sheet (containing just c.100 rows) in case this helps at all. In theory the H rows would be "High", I rows would be "Low", and J rows would be "Average" (all estimates pulled from the web) -> K will be where I create a percentage from those figures (I can do this, only mentioning it to give understanding for the layout). I have Hidden Irrelevant rows.

I appreciate any help you are able to offer

S&P500 Stock Data - Macro (version 1).xlsb
ABHIJKR
1SymbolTickerExternalData_1URL
2AGILENT TECHNOLOGIES, INC. (XNYS:A)Ahttps://www.marketwatch.com/investing/stock/A/analystestimates?mod=mw_quote_tab
3AMERICAN AIRLINES GROUP INC. (XNAS:AAL)AALhttps://www.marketwatch.com/investing/stock/AAL/analystestimates?mod=mw_quote_tab
4ADVANCE AUTO PARTS, INC. (XNYS:AAP)AAPhttps://www.marketwatch.com/investing/stock/AAP/analystestimates?mod=mw_quote_tab
5APPLE INC. (XNAS:AAPL)AAPLhttps://www.marketwatch.com/investing/stock/AAPL/analystestimates?mod=mw_quote_tab
6ABBVIE INC. (XNYS:ABBV)ABBVhttps://www.marketwatch.com/investing/stock/ABBV/analystestimates?mod=mw_quote_tab
7AMERISOURCEBERGEN CORPORATION (XNYS:ABC)ABChttps://www.marketwatch.com/investing/stock/ABC/analystestimates?mod=mw_quote_tab
8ABIOMED, INC. (XNAS:ABMD)ABMDhttps://www.marketwatch.com/investing/stock/ABMD/analystestimates?mod=mw_quote_tab
9ABBOTT LABORATORIES (XNYS:ABT)ABThttps://www.marketwatch.com/investing/stock/ABT/analystestimates?mod=mw_quote_tab
10ACCENTURE PUBLIC LIMITED COMPANY (XNYS:ACN)ACNhttps://www.marketwatch.com/investing/stock/ACN/analystestimates?mod=mw_quote_tab
11ADOBE INC. (XNAS:ADBE)ADBEhttps://www.marketwatch.com/investing/stock/ADBE/analystestimates?mod=mw_quote_tab
12ANALOG DEVICES, INC. (XNAS:ADI)ADIhttps://www.marketwatch.com/investing/stock/ADI/analystestimates?mod=mw_quote_tab
13ARCHER-DANIELS-MIDLAND COMPANY (XNYS:ADM)ADMhttps://www.marketwatch.com/investing/stock/ADM/analystestimates?mod=mw_quote_tab
14AUTOMATIC DATA PROCESSING, INC. (XNAS:ADP)ADPhttps://www.marketwatch.com/investing/stock/ADP/analystestimates?mod=mw_quote_tab
15AUTODESK, INC. (XNAS:ADSK)ADSKhttps://www.marketwatch.com/investing/stock/ADSK/analystestimates?mod=mw_quote_tab
16AMEREN CORPORATION (XNYS:AEE)AEEhttps://www.marketwatch.com/investing/stock/AEE/analystestimates?mod=mw_quote_tab
17AMERICAN ELECTRIC POWER COMPANY, INC. (XNAS:AEP)AEPhttps://www.marketwatch.com/investing/stock/AEP/analystestimates?mod=mw_quote_tab
18THE AES CORPORATION (XNYS:AES)AEShttps://www.marketwatch.com/investing/stock/AES/analystestimates?mod=mw_quote_tab
19AFLAC INCORPORATED (XNYS:AFL)AFLhttps://www.marketwatch.com/investing/stock/AFL/analystestimates?mod=mw_quote_tab
20AMERICAN INTERNATIONAL GROUP, INC. (XNYS:AIG)AIGhttps://www.marketwatch.com/investing/stock/AIG/analystestimates?mod=mw_quote_tab
21ASSURANT, INC. (XNYS:AIZ)AIZhttps://www.marketwatch.com/investing/stock/AIZ/analystestimates?mod=mw_quote_tab
22ARTHUR J. GALLAGHER & CO. (XNYS:AJG)AJGhttps://www.marketwatch.com/investing/stock/AJG/analystestimates?mod=mw_quote_tab
23AKAMAI TECHNOLOGIES, INC. (XNAS:AKAM)AKAMhttps://www.marketwatch.com/investing/stock/AKAM/analystestimates?mod=mw_quote_tab
24ALBEMARLE CORPORATION (XNYS:ALB)ALBhttps://www.marketwatch.com/investing/stock/ALB/analystestimates?mod=mw_quote_tab
25ALIGN TECHNOLOGY, INC. (XNAS:ALGN)ALGNhttps://www.marketwatch.com/investing/stock/ALGN/analystestimates?mod=mw_quote_tab
26ALASKA AIR GROUP, INC. (XNYS:ALK)ALKhttps://www.marketwatch.com/investing/stock/ALK/analystestimates?mod=mw_quote_tab
27THE ALLSTATE CORPORATION (XNYS:ALL)ALLhttps://www.marketwatch.com/investing/stock/ALL/analystestimates?mod=mw_quote_tab
28ALLEGION PUBLIC LIMITED COMPANY (XNYS:ALLE)ALLEhttps://www.marketwatch.com/investing/stock/ALLE/analystestimates?mod=mw_quote_tab
29ALEXION PHARMACEUTICALS, INC. (XNAS:ALXN)ALXNhttps://www.marketwatch.com/investing/stock/ALXN/analystestimates?mod=mw_quote_tab
30APPLIED MATERIALS, INC. (XNAS:AMAT)AMAThttps://www.marketwatch.com/investing/stock/AMAT/analystestimates?mod=mw_quote_tab
31Amcor plc (XNYS:AMCR)AMCRhttps://www.marketwatch.com/investing/stock/AMCR/analystestimates?mod=mw_quote_tab
32ADVANCED MICRO DEVICES, INC. (XNAS:AMD)AMDhttps://www.marketwatch.com/investing/stock/AMD/analystestimates?mod=mw_quote_tab
33AMETEK, INC. (XNYS:AME)AMEhttps://www.marketwatch.com/investing/stock/AME/analystestimates?mod=mw_quote_tab
34AMGEN INC. (XNAS:AMGN)AMGNhttps://www.marketwatch.com/investing/stock/AMGN/analystestimates?mod=mw_quote_tab
35AMERIPRISE FINANCIAL, INC. (XNYS:AMP)AMPhttps://www.marketwatch.com/investing/stock/AMP/analystestimates?mod=mw_quote_tab
36AMERICAN TOWER CORPORATION (XNYS:AMT)AMThttps://www.marketwatch.com/investing/stock/AMT/analystestimates?mod=mw_quote_tab
37AMAZON.COM, INC. (XNAS:AMZN)AMZNhttps://www.marketwatch.com/investing/stock/AMZN/analystestimates?mod=mw_quote_tab
38ARISTA NETWORKS, INC. (XNYS:ANET)ANEThttps://www.marketwatch.com/investing/stock/ANET/analystestimates?mod=mw_quote_tab
39ANSYS, INC. (XNAS:ANSS)ANSShttps://www.marketwatch.com/investing/stock/ANSS/analystestimates?mod=mw_quote_tab
40ANTHEM, INC. (XNYS:ANTM)ANTMhttps://www.marketwatch.com/investing/stock/ANTM/analystestimates?mod=mw_quote_tab
41Aon plc (XNYS:AON)AONhttps://www.marketwatch.com/investing/stock/AON/analystestimates?mod=mw_quote_tab
42A. O. SMITH CORPORATION (XNYS:AOS)AOShttps://www.marketwatch.com/investing/stock/AOS/analystestimates?mod=mw_quote_tab
43APA CORPORATION (XNAS:APA)APAhttps://www.marketwatch.com/investing/stock/APA/analystestimates?mod=mw_quote_tab
44AIR PRODUCTS AND CHEMICALS, INC. (XNYS:APD)APDhttps://www.marketwatch.com/investing/stock/APD/analystestimates?mod=mw_quote_tab
45AMPHENOL CORPORATION (XNYS:APH)APHhttps://www.marketwatch.com/investing/stock/APH/analystestimates?mod=mw_quote_tab
46APTIV PLC (XNYS:APTV)APTVhttps://www.marketwatch.com/investing/stock/APTV/analystestimates?mod=mw_quote_tab
47ALEXANDRIA REAL ESTATE EQUITIES, INC. (XNYS:ARE)AREhttps://www.marketwatch.com/investing/stock/ARE/analystestimates?mod=mw_quote_tab
48ATMOS ENERGY CORPORATION (XNYS:ATO)ATOhttps://www.marketwatch.com/investing/stock/ATO/analystestimates?mod=mw_quote_tab
49ACTIVISION BLIZZARD, INC. (XNAS:ATVI)ATVIhttps://www.marketwatch.com/investing/stock/ATVI/analystestimates?mod=mw_quote_tab
50AVALONBAY COMMUNITIES, INC. (XNYS:AVB)AVBhttps://www.marketwatch.com/investing/stock/AVB/analystestimates?mod=mw_quote_tab
51Broadcom Inc. (XNAS:AVGO)AVGOhttps://www.marketwatch.com/investing/stock/AVGO/analystestimates?mod=mw_quote_tab
52AVERY DENNISON CORPORATION (XNYS:AVY)AVYhttps://www.marketwatch.com/investing/stock/AVY/analystestimates?mod=mw_quote_tab
53AMERICAN WATER WORKS COMPANY, INC. (XNYS:AWK)AWKhttps://www.marketwatch.com/investing/stock/AWK/analystestimates?mod=mw_quote_tab
54AMERICAN EXPRESS COMPANY (XNYS:AXP)AXPhttps://www.marketwatch.com/investing/stock/AXP/analystestimates?mod=mw_quote_tab
55AUTOZONE, INC. (XNYS:AZO)AZOhttps://www.marketwatch.com/investing/stock/AZO/analystestimates?mod=mw_quote_tab
56THE BOEING COMPANY (XNYS:BA)BAhttps://www.marketwatch.com/investing/stock/BA/analystestimates?mod=mw_quote_tab
57BANK OF AMERICA CORPORATION (XNYS:BAC)BAChttps://www.marketwatch.com/investing/stock/BAC/analystestimates?mod=mw_quote_tab
58BAXTER INTERNATIONAL INC. (XNYS:BAX)BAXhttps://www.marketwatch.com/investing/stock/BAX/analystestimates?mod=mw_quote_tab
59Best Buy Co., Inc. (XNYS:BBY)BBYhttps://www.marketwatch.com/investing/stock/BBY/analystestimates?mod=mw_quote_tab
60BECTON, DICKINSON AND COMPANY (XNYS:BDX)BDXhttps://www.marketwatch.com/investing/stock/BDX/analystestimates?mod=mw_quote_tab
61FRANKLIN RESOURCES, INC. (XNYS:BEN)BENhttps://www.marketwatch.com/investing/stock/BEN/analystestimates?mod=mw_quote_tab
62BROWN-FORMAN CORPORATION (XNYS:BF.B)BF.Bhttps://www.marketwatch.com/investing/stock/BF.B/analystestimates?mod=mw_quote_tab
63BIOGEN INC. (XNAS:BIIB)BIIBhttps://www.marketwatch.com/investing/stock/BIIB/analystestimates?mod=mw_quote_tab
64BIO-RAD LABORATORIES, INC. (XNYS:BIO)BIOhttps://www.marketwatch.com/investing/stock/BIO/analystestimates?mod=mw_quote_tab
65THE BANK OF NEW YORK MELLON CORPORATION (XNYS:BK)BKhttps://www.marketwatch.com/investing/stock/BK/analystestimates?mod=mw_quote_tab
66BOOKING HOLDINGS INC. (XNAS:BKNG)BKNGhttps://www.marketwatch.com/investing/stock/BKNG/analystestimates?mod=mw_quote_tab
67BAKER HUGHES COMPANY (XNYS:BKR)BKRhttps://www.marketwatch.com/investing/stock/BKR/analystestimates?mod=mw_quote_tab
68BLACKROCK, INC. (XNYS:BLK)BLKhttps://www.marketwatch.com/investing/stock/BLK/analystestimates?mod=mw_quote_tab
69BALL CORPORATION (XNYS:BLL)BLLhttps://www.marketwatch.com/investing/stock/BLL/analystestimates?mod=mw_quote_tab
70BRISTOL-MYERS SQUIBB COMPANY (XNYS:BMY)BMYhttps://www.marketwatch.com/investing/stock/BMY/analystestimates?mod=mw_quote_tab
71BROADRIDGE FINANCIAL SOLUTIONS, INC. (XNYS:BR)BRhttps://www.marketwatch.com/investing/stock/BR/analystestimates?mod=mw_quote_tab
72BERKSHIRE HATHAWAY INC. (XNYS:BRK.B)BRK.Bhttps://www.marketwatch.com/investing/stock/BRK.B/analystestimates?mod=mw_quote_tab
73BOSTON SCIENTIFIC CORPORATION (XNYS:BSX)BSXhttps://www.marketwatch.com/investing/stock/BSX/analystestimates?mod=mw_quote_tab
74BORGWARNER INC. (XNYS:BWA)BWAhttps://www.marketwatch.com/investing/stock/BWA/analystestimates?mod=mw_quote_tab
75BOSTON PROPERTIES, INC. (XNYS:BXP)BXPhttps://www.marketwatch.com/investing/stock/BXP/analystestimates?mod=mw_quote_tab
76CITIGROUP INC. (XNYS:C)Chttps://www.marketwatch.com/investing/stock/C/analystestimates?mod=mw_quote_tab
77CONAGRA BRANDS, INC. (XNYS:CAG)CAGhttps://www.marketwatch.com/investing/stock/CAG/analystestimates?mod=mw_quote_tab
78CARDINAL HEALTH, INC. (XNYS:CAH)CAHhttps://www.marketwatch.com/investing/stock/CAH/analystestimates?mod=mw_quote_tab
79CARRIER GLOBAL CORPORATION (XNYS:CARR)CARRhttps://www.marketwatch.com/investing/stock/CARR/analystestimates?mod=mw_quote_tab
80CATERPILLAR INC. (XNYS:CAT)CAThttps://www.marketwatch.com/investing/stock/CAT/analystestimates?mod=mw_quote_tab
81Chubb Ltd (XNYS:CB)CBhttps://www.marketwatch.com/investing/stock/CB/analystestimates?mod=mw_quote_tab
82Cboe Global Markets, Inc. (BATS:CBOE)CBOEhttps://www.marketwatch.com/investing/stock/CBOE/analystestimates?mod=mw_quote_tab
83CBRE GROUP, INC. (XNYS:CBRE)CBREhttps://www.marketwatch.com/investing/stock/CBRE/analystestimates?mod=mw_quote_tab
84CROWN CASTLE INTERNATIONAL CORP. (XNYS:CCI)CCIhttps://www.marketwatch.com/investing/stock/CCI/analystestimates?mod=mw_quote_tab
85Carnival Corp (XNYS:CCL)CCLhttps://www.marketwatch.com/investing/stock/CCL/analystestimates?mod=mw_quote_tab
86CADENCE DESIGN SYSTEMS, INC. (XNAS:CDNS)CDNShttps://www.marketwatch.com/investing/stock/CDNS/analystestimates?mod=mw_quote_tab
87CDW CORPORATION (XNAS:CDW)CDWhttps://www.marketwatch.com/investing/stock/CDW/analystestimates?mod=mw_quote_tab
88CELANESE CORPORATION (XNYS:CE)CEhttps://www.marketwatch.com/investing/stock/CE/analystestimates?mod=mw_quote_tab
89CERNER CORPORATION (XNAS:CERN)CERNhttps://www.marketwatch.com/investing/stock/CERN/analystestimates?mod=mw_quote_tab
90CF INDUSTRIES HOLDINGS, INC. (XNYS:CF)CFhttps://www.marketwatch.com/investing/stock/CF/analystestimates?mod=mw_quote_tab
91CITIZENS FINANCIAL GROUP, INC. (XNYS:CFG)CFGhttps://www.marketwatch.com/investing/stock/CFG/analystestimates?mod=mw_quote_tab
92CHURCH & DWIGHT CO., INC. (XNYS:CHD)CHDhttps://www.marketwatch.com/investing/stock/CHD/analystestimates?mod=mw_quote_tab
93C.H. ROBINSON WORLDWIDE, INC. (XNAS:CHRW)CHRWhttps://www.marketwatch.com/investing/stock/CHRW/analystestimates?mod=mw_quote_tab
94CHARTER COMMUNICATIONS, INC. (XNAS:CHTR)CHTRhttps://www.marketwatch.com/investing/stock/CHTR/analystestimates?mod=mw_quote_tab
95CIGNA CORPORATION (XNYS:CI)CIhttps://www.marketwatch.com/investing/stock/CI/analystestimates?mod=mw_quote_tab
96CINCINNATI FINANCIAL CORPORATION (XNAS:CINF)CINFhttps://www.marketwatch.com/investing/stock/CINF/analystestimates?mod=mw_quote_tab
97COLGATE-PALMOLIVE COMPANY (XNYS:CL)CLhttps://www.marketwatch.com/investing/stock/CL/analystestimates?mod=mw_quote_tab
98THE CLOROX COMPANY (XNYS:CLX)CLXhttps://www.marketwatch.com/investing/stock/CLX/analystestimates?mod=mw_quote_tab
99COMERICA INCORPORATED (XNYS:CMA)CMAhttps://www.marketwatch.com/investing/stock/CMA/analystestimates?mod=mw_quote_tab
100COMCAST CORPORATION (XNAS:CMCSA)CMCSAhttps://www.marketwatch.com/investing/stock/CMCSA/analystestimates?mod=mw_quote_tab
S&P500 Stock Data
Cell Formulas
RangeFormula
B2:B100B2=A2.[Ticker symbol]
R2:R100R2="https://www.marketwatch.com/investing/stock/"&B2&"/analystestimates?mod=mw_quote_tab"
 
Upvote 0
If you entered the vba code and started the code and nothing happens I don't know how to help you anymore.
 
Upvote 0
Did you investigate the link I provided in Post #2.
 
Upvote 0
Did you investigate the link I provided in Post #2.
Alan,

Yes I did, thank you. it doesn't quite have provide I'm after, and I am able to replicate the majority of it myself, however my main issue is that my university has blocked add-ins (I have to go through the IT department to get it, which I can't do off campus).

Thank you for your suggestion, but I am still looking to scrape the web personally.

Happy Easter (if you celebrate)!

Arthur
 
Upvote 0
@ *arthur72j

Does the following help?

VBA Code:
Range("H2").Value = Doc.getElementsByClassName("table__cell w25 ")(11)    ' Get High Price Target
Range("I2").Value = Doc.getElementsByClassName("table__cell w25 ")(13)    ' Get Low Price Target
Range("J2").Value = Doc.getElementsByClassName("table__cell w25 ")(14)    ' Get Average Price Target
 
Upvote 0

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top