How to fetch values from either of the sites!?

[mittoo]

Hello,

I specifically need Market Cap Value and Stock Price Value from either of the sites

Pennar Industries Ltd - Screener (here on this site Market Cap and Current Price is clearly mentioned) (this site updates at the end of the day i suppose)

Stock Share Price | Get Quote | BSE (here M Cap full and Stock price value is below the stock price name) (this site changes updates in real time)

See image file with black boxes that needs to be fetched

I need, not just for this company, but less than 1500 companies in excel.

Any ideas or suggestions? Thanks

 

[Rijnsent]

Hi Mittoo,
you're probably looking for the term "web scraping with VBA", see e.g. this site: https://www.myonlinetraininghub.com/web-scraping-with-vba or this site: Scraping data from website using vba
Cheers,
Koen

 

[mittoo]

Hi! Rijnsent. Thanks for the link! I looked up into that. Former is much more helpful.

Secondly I punched in the following code:

Sub FetchData()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;Atul Auto Ltd - Screener", Destination:=Range( _
"$A$1"))
.Name = "www"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

I have less than 1500 stocks as mentioned. So with this approach I was able to fetch approx 150 stocks related values with additional help taken from other MS Excel functions in one go.

The problem comes when I tried to run the code for all stocks. Same code repeated 1465 times with different stocks values.

It gives me Compile error: Procedure too large. I suppose not able to handle that many requests.

Any idea to overcome that hurdle??

 

[Rijnsent]

Can you post your code here? "Too large" means that you probably copy-pasted your code a bunch of times and a loop would be a smarter thing to do. See e.g. this post: Getting error Procedure too large in VBA Macros (Excel) Please do so in CODE tags: [CODE=vba]CODE HERE[/CODE]
Thanks, Koen

 

[mittoo]

Honestly I dont know much about VBA. not my thing. And I have taken a long route for sure. I dont even know how to put that loop.
Its just through browsing I was able to fetch that. And you are probably right copy paste. the 150 stock values take around less than 5 minutes. so another 9 rounds would fetch me all stocks values. and with the help of other functions. I am able to run things smoothly. That things happening in one go seems impossible and same when I added 100 more into the list.
I gave 200 cells gap in between them so that I can fetch everything as to what I tested. Following is the code:

Sub FetchData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.screener.in/company/531795/", Destination:=Range( _
        "$A$1"))
        .Name = "www"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.screener.in/company/532977/", Destination:=Range( _
        "$A$200"))
        .Name = "www"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
        With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.screener.in/company/505200/", Destination:=Range( _
        "$A$400"))
        .Name = "www"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
        With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.screener.in/company/540153/", Destination:=Range( _
        "$A$600"))
        .Name = "www"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
        With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.screener.in/company/500182/", Destination:=Range( _
        "$A$800"))
        .Name = "www"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Rijnsent]

Hi mittoo,
if VBA is not your thing, make it your thing... If you want to scrape all that data, you will probably need some VBA and it's a really practical skill to have. Get started with e.g. Free VBA Training Course or Excel VBA Programming - a free course for complete beginners
Having said that, say you have your company ids in a list somewhere in your Excel, you could do the following (notice: it's rather inefficient to do it this way, but can get the job done). More efficient would be to have only one QueryTable, update the source of that table in a loop and just copy-paste the values you need in that same loop, as a spreadsheet with 1500 external links will have a high chance of crashing when you try to refresh.
Koen

Sub GetMyData()

Set Sht = Worksheets("Sheet1")
StartRow = 2
EndRow = 5

For r = StartRow To EndRow
    idValue = Sht.Range("A" & r).Value
    Url = "URL;https://www.screener.in/company/" & idValue & "/"
    ResultRange = "$C$" & r * 200
    
    With Sht.QueryTables.Add(Connection:=Url, Destination:=Sht.Range(ResultRange))
        .Name = "www"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True
    End With
    Sht.Range("B" & r).FormulaR1C1 = "=R" & r * 200 + 10 & "C3"
Next r

End Sub

 

[mittoo]

Thanks Koen! for the code and links. I am working on that now.