URGENT! Collecting data from web

[GustavoR]

Hi guys, how can i get a code to access the website "http://www3.tesouro.gov.br/tesouro_direto/consulta_titulos_novosite/consultatitulos.asp" and get the "NTNB Principal 150519", "NTNB Principal 150824", "NTNB Principal 150535" and "LTN 010118" prices (the penultimate column)? Powerquery simply doesnt work.

 

[vds1]

This works for me,

Sub Get_Data_From_Web_Macro()


    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www3.tesouro.gov.br/tesouro_direto/consulta_titulos_novosite/consultatitulos.asp" _
        , Destination:=Range("$A$1"))
        .Name = "consultatitulos"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[GustavoR]

This works for me,

Sub Get_Data_From_Web_Macro()


    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www3.tesouro.gov.br/tesouro_direto/consulta_titulos_novosite/consultatitulos.asp" _
        , Destination:=Range("$A$1"))
        .Name = "consultatitulos"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

It, then, copy the whole table, right? In my case, it would be more useful if i could, for example, dim a variable as the asset price.

 

[vds1]

Handling Internet with VBA is quite complex. Here is one approach you can follow,

1). Have a separate sheet to get data from web and refresh it whenever you want to. Name the sheet as "Raw Dump".

2). Use vlookup formulas to get data from the "Raw Dump"

 

[GustavoR]

Handling Internet with VBA is quite complex. Here is one approach you can follow,

1). Have a separate sheet to get data from web and refresh it whenever you want to. Name the sheet as "Raw Dump".

2). Use vlookup formulas to get data from the "Raw Dump"

I already have this:

Sub Atualizar()
'
' Esta macro atualiza os valores de mercado dos ativos
'
confere = MsgBox("Deseja atualizar a carteira para o valor de mercado?", vbYesNo, "Atualizar?")
Select Case confere
Case vbYes
Case vbNo
Exit Sub
End Select


ActiveWorkbook.RefreshAll
ativosini = Range("a1").Value
Dim IE As New InternetExplorer
'IE.Visible = True 'Tire o comentário para re-exibir o IE
Dim Doc As HTMLDocument
For i = ativosini To ativosini + 9 'ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ticker = Trim$(Range("A" & i).Value)
MsgBox 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 + 0
' MsgBox quote
If quote = 0 Then
MsgBox "Busca de dados não obteve sucesso. Verifique a conexão com a internet."
Exit Sub
Else
End If
ActiveSheet.Range("B" & i).Value = quote
Next
IE.Quit
retorno = Format(Range("D2").Value, "percent")
MsgBox "Carteira atualizada com sucesso. O retorno atual é de " & retorno, vbInformation, "Atualização concluída."
End Sub


that works perfectly. but i couldnt manage to get from this website (Tesouro Direto - Consulta Ttulos) the exact data i want (second last column, rows 4, 6, 8 and 12)