Automatically extract website data to a spreadsheet

[Corried]

Hello, Thank you for sharing..

I am looking for courses on how to automatically extract data from a website to an excel spreadsheet.

The excel file contains fifty links. Each link has the same process, to create a stock market report for fifty companies. Use macro and chrome to generate data.

Can anyone show me a step by step process on how I can do this?

Please watch the video and Excel workbook below to learn more.

Example STOCK REPORT CARD

Sheet2 CORRIE STOCK REPORT CARD Websites Top 100,Company,Symbol,P/E,Price,Sales Growth Yr Over Yr,Sales, Revenue, Net Income in B=billion or M=million,52-Week Low & High,5 Year High & Low,Market Cap; # Shares,Debt/Equity Ratio,Net Profit,Company: Insider Trading,Operating Margins,Description,New...

docs.google.com

I hope you all understand.

Cheers

 

[Worf]

The MSN site has a new popup window; I will have to alter the code to programmatically close it…

 

[Corried]

The MSN site has a new popup window; I will have to alter the code to programmatically close it…

wow. hummm. Okay then. Thanks

 

[Worf]

I solved the popup issue by opening a new tab, where it does not load.

For our last test, the partial code below should be enough.

If it breaks, tell me the line.

Public driver As New ChromeDriver

Sub Money29b()
Dim pt As WebElement, key As New Selenium.Keys, s$
driver.get "https://www.msn.com/en-us/money/stockdetails/fi-bjqejc?duration=1D"
Application.Wait Now + TimeValue("0:00:16")
driver.ExecuteScript "window.open('https://www.msn.com/en-us/money/stockdetails/fi-bjqejc?duration=1D','_blank');"
driver.SwitchToNextWindow
Application.Wait Now + TimeValue("0:00:26")
Set pt = driver.FindElementByXPath("//*[@id=""searchBox""]/input")
pt.Click
pt.SendKeys ActiveSheet.[c2]
Application.Wait Now + TimeValue("0:00:06")
DoEvents
Set pt = driver.FindElementByCss("#searchBox > span > span > svg")
pt.Click
Application.Wait Now + TimeValue("0:00:06")
DoEvents
[a2] = driver.URL
[b2] = driver.FindElementByCss("#root > div:nth-child(1) > div > div.mainContentLayout-DS-EntryPoint1-1 > div > div >" & _
" div:nth-child(1) > div > div.firstSection-DS-EntryPoint1-1 > div > div.title-DS-EntryPoint1-1 > span.displayNameWithBtn-DS-EntryPoint1-1").Text
[d2] = driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[3]/div/div[2]/div[7]/div[2]").Text
[e2] = driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[1]/div/div[4]/div[1]/div/div[1]").Text
'[e2] = driver.FindElementByCss("#root > div:nth-child(1) > div > div.mainContentLayout-DS-EntryPoint1-1 >" & _
" div > div > div:nth-child(1) > div > div.secondSection-DS-EntryPoint1-1 > div.priceInfo-DS-EntryPoint1-1 > div >" & _
" div.mainPrice.color_red-DS-EntryPoint1-1").Text
s = driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[3]/div/div[1]/div[2]/div/div[3]/span[2]").Text
's = driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[2]/div/div[2]/div[1]/div[2]/div/div[3]/span[2]").Text
s = s & " / " & driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[3]/div/div[1]/div[2]/div/div[3]/span[1]").Text
[h2] = s
[j2] = driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[3]/div/div[2]/div[4]/div[2]").Text
' 5-year
driver.FindElementByXPath("//*[@id=""root""]/div[1]/div/div[5]/div/div/div[2]/div/div[1]/div[2]/div/div/button[8]").Click
Application.Wait Now + TimeValue("0:00:06")
MsgBox "End of first part"
End Sub

 

[Corried]

For our last test, the partial code below should be enough.

Work. Good day,

I think I have something here. I only got part of B2GOLD CORP filled. Please take a look.
Finally some things are working End of 1st Part.webm

 

[Corried]

solved the popup issue by opening a new tab, where it does

Good day Worf. I am sorry. I am not sure if you got my message. Your last code have worked successfully. The only problem the code did not pasted all the data in the cells. The data was only paste for one company...

I was not sure if you design the code to worked for testing. But I am glad not to see any errors involved... Thanks

 

[Worf]

Yes, that is only test code. We need to run it again in a couple of days to see if it breaks, unfortunately I think it will.

My theory is that the HTML source code continuously changes.

 

[Corried]

Yes, that is only test code. We need to run it again in a couple of days to see if it breaks, unfortunately I think it will.

My theory is that the HTML source code continuously changes.

Hello Worf.

I did no see ofdjhi

Yes, that is only test code. We need to run it again in a couple of days to see if it breaks, unfortunately I think it will.

My theory is that the HTML source code continuously changes.

 

[Corried]

Yes, that is only test code. We need to run it again in a couple of days to see if it breaks, unfortunately I think it will.

My theory is that the HTML source code continuously changes.

Hello Worf.

I am sorry, yesterday while typing my keyboard, not functioning the way it should suddenly.
Please forgive me. I have not seen your email notification since last Tuesday...
I have tested the macro again. It's functioning, but still missing some pieces of the puzzle.

 

[Worf]

I ran it today and got an error at D2.
I will try to find out what is happening during the week.

 

[Corried]

I ran it today and got an error at D2.
I will try to find out what is happening during the week.

Okay thanks...