URGENT - extracting data from website

G

GustavoR

New Member
Joined
Dec 8, 2014
Messages
37
Guys, i've been trying this for a long time and now i give up trying lol.

i want a macro that access the website

https://br.financas.yahoo.com/q?s=pmam3&ql=1

copy the value of the quote, then paste at the worksheet

after that, go to the "digite o símbolo" box, search for another cote and repeat the process.

I have tried the .getelementbyid, but it simply doesnt work.

Can you please help me?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
I assume you have tickers somewhere on your worksheet, correct?
if so where are they housed?
i normally use getelementbytagname and find the right index number, then loop through the worksheet and navigating to different webpages.
 
Upvote 0
Gilliam said:
I assume you have tickers somewhere on your worksheet, correct?
if so where are they housed?
i normally use getelementbytagname and find the right index number, then loop through the worksheet and navigating to different webpages.
Click to expand...

Here is the table:

current price of portfolio
stockcurrentqntyTOTAL
PMAM32,215001105
EZTC3F22701540
HYPE3F16,36821341,52
RAPT4F5,462361288,56
ABCB4F12,9929376,71
KROT3F16,55781290,9
6942,69

<colgroup><col span="4"></colgroup><tbody>
</tbody>


I want the macro take the current stock prices at that website
 
Upvote 0
I can probably help you you, but i did run into a problem when the ticker doesnt exist on the website :s, it shows the price for the above stock, but if its the exact same, you can clearly see there's an error and you should investigate yourself...
Code: 
Sub hello()   
    Dim IE As New InternetExplorer
    'IE.Visible = True   'don't show the opening up of internet explorer
    Dim Doc As HTMLDocument
    
    For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
        ticker = Range("A" & i).Value
        IE.navigate "https://br.financas.yahoo.com/q?s=" & ticker & "&ql=1"
        
        Do
            DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
        On Error Resume Next
        quote = IE.document.getElementsByTagName("span")(24).innertext
        Sheets(1).Range("B" & i).Value = quote
    Next
    IE.Quit


End Sub

*With microsoft internet controls and microsoft html object library enabled*
 
Upvote 0
Gilliam said:
I can probably help you you, but i did run into a problem when the ticker doesnt exist on the website :s, it shows the price for the above stock, but if its the exact same, you can clearly see there's an error and you should investigate yourself...
Code: 
Sub hello()   
    Dim IE As New InternetExplorer
    'IE.Visible = True   'don't show the opening up of internet explorer
    Dim Doc As HTMLDocument
    
    For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
        ticker = Range("A" & i).Value
        IE.navigate "https://br.financas.yahoo.com/q?s=" & ticker & "&ql=1"
        
        Do
            DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
        On Error Resume Next
        quote = IE.document.getElementsByTagName("span")(24).innertext
        Sheets(1).Range("B" & i).Value = quote
    Next
    IE.Quit


End Sub

*With microsoft internet controls and microsoft html object library enabled*
Click to expand...

I am already extremely thankfull, but the macro returned an error at the first line. is this what you were trying to tell me?
 
Upvote 0
no, that shouldnt have been a problem, maybe try replacing...

ticker = Range("A" & i).Value
Click to expand...

with

Code: 
[COLOR=#333333][I]ticker = trim$(Range("A" & i).Value)[/I][/COLOR]

as there may have been an unnecessary space in the cell:s

UPDATE:

this code is a little more robust, and changes the quotes to an actual value instead of text...
Code: 
Sub Refresh()    Dim IE As New InternetExplorer
    'IE.Visible = True   'don't show the opening up of internet explorer
    Dim Doc As HTMLDocument
    For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ticker = Trim$(Range("A" & i).Value)
        IE.navigate "https://br.financas.yahoo.com/q?s=" & ticker & "&ql=1"
        Do
            DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
        On Error Resume Next
        quote = IE.document.getElementsByTagName("span")(24).innertext
        Sheets(1).Range("B" & i).Value = Application.WorksheetFunction.Substitute(quote, ",", ".") + 0
    Next
    IE.Quit
End Sub
 
Last edited:
Upvote 0
Nice. I tried it. i dont know why but the "Dim IE As New InternetExplorer" line doesnt work.

The previous code you sent, i made some changes and it worked for the first line, but not the others.
 
Upvote 0
do you have microsoft internet controls enabled?
if not, VBA > Tools > References > Microsoft internet controls > OK
using the lastest macro i get...

PMAM32.19
AAPL110.57
HYPE3F16.14
RAPT4F5.4
ABCB4F12.8
KROT3F16.56

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
I enabled both "microsoft internet controls and microsoft html object library" and it still doesnt work. It navigates to the first page, pastes at the sheet "219" (without the dot, nor comma), navigates to the second page and stops working.

Here is the code. Dont mind the commented ones, i was trying another way


Sub Refresh()
Dim IE As New InternetExplorer
'Dim IE As Object
'
'Set IE = CreateObject("InternetExplorer.Application")


IE.Visible = True 'don't show the opening up of internet explorer
Dim Doc As HTMLDocument
For i = 3 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ticker = Trim$(Range("A" & i).Value)
IE.navigate "https://br.financas.yahoo.com/q?s=" & ticker & "&ql=1"
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
'Do While IE.Busy
' Application.Wait DateAdd("s", 1, Now)
' Loop
On Error Resume Next
quote = IE.document.getElementsByTagName("span")(24).innertext
Sheets(1).Range("B" & i).Value = Application.WorksheetFunction.Substitute(quote, ",", ".") + 0
Next
IE.Quit
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Anigito
  • Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
417
Anigito
Anigito
A
  • Question
Macro to Combine Data from Multiple Sheets
Replies
2
Views
174
ashringg
A
A
  • Solved
Help with macros to open many files, and extract data. Need help with match / find.
Replies
9
Views
961
johnnyL
J
S
  • Question
Live data extraction from NSE website
Replies
0
Views
2K
steffin
S
A
  • Solved
Extracting string from PDF and inserting into Excel using VBA
Replies
8
Views
10K
Hamster1337
H

Forum statistics

Threads
1,214,822
Messages
6,121,772
Members
449,049
Latest member
greyangel23

Share this page

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