URGENT - extracting data from website

[GustavoR]

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?

 

[GustavoR]

that's very awkward . i downloaded your file. erased the prices. run the macro. it pastes the first value (2,19). opens the eztc3f page and stops!

 

[Gilliam]

use this 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)
        Debug.Print ticker
        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
        Debug.Print quote
    Next
    IE.Quit
End Sub

and post here what the immediate window print out upon completion of the macro..
Should be...
PMAM3
2,16
EZTC3f
20,68
HYPE3F
16,01
RAPT4F
5,36
ABCB4F
12,85
KROT3F
16,16

 

[GustavoR]

Tickers
PMAM3	2,16
EZTC3F	20,68
HYPE3F	16,07
RAPT4F	Adicionar ao portfólio
ABCB4F	12,85
KROT3F	16,16

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

It worked!!!! But at the RAPT4F, it returned the text "Adicionar ao portfólio", which means "Add to the portfolio".

 

[GustavoR]

I tried a couple more times and it worked. Can i say that i love you bro? lol

 

[Gilliam]

A) very weird, but yay!
B) uhhhh I suppose

also, I dont think you'd have any issues if you used the bloomberg website, but i tested th quotes there and the tickers are slightly different, so i guess you could stick with the bug-filled macro currently. But it kinda works lol.

 

[GustavoR]

Thank you very much. I am now stably using the macro but it insists on pasting the "quote" as text, and i need number. i tried this:

Range("b88:b95").NumberFormat = "#"

and this

Range("b88:b95").Select
Selection.Style = "Currency"

but none of them are working. do you know why?

 

[Gilliam]

Thank you very much. I am now stably using the macro but it insists on pasting the "quote" as text, and i need number. i tried this:

Range("b88:b95").NumberFormat = "#"

and this

Range("b88:b95").Select
Selection.Style = "Currency"

but none of them are working. do you know why?

Change this line

[COLOR=#333333]quote = IE.document.getElementsByTagName("span")(24).innertext[/COLOR]

to this line

Quote = Replace(IE.document.getElementsByTagName("span")(24).innertext, ",", ".") + 0

 

[GustavoR]

Excellent! Thank you one more time. Could you explain me what this "(24)" stands for? I am trying to comprehend this code and get another website. But i am not able to understand the .getelementsbytagname thing, how it exactly works. For example, i want to navigate to "http://www3.tesouro.gov.br/tesouro_direto/consulta_titulos/consultatitulos.asp" and get the "NTNB Principal 150535", "NTNB Principal 150824" and the "LTN 010118" prices, at the fifth column. I saw that its tag name is "<tr>" but do i manage to refer to it correctly?