Get dollar to peso exchange rate from a website for any given date

[namariegaudi]

Hi, this coding is totally new to me
I have a website our company usually goes to get the official exchange rate of dollar /peso (there are 2 values, FIX and Interbancario) I would need the later. Is there a way to build a macro that given a date in Excel goes to the website listed here:

https://www.banxico.org.mx/tipcamb/main.do?page=tip&idioma=sp

and provide the exchange rate and put it in a cell within the Excel file that contains the macro?

Regards

 

[James006]

Hi,

You can take a look at this method with Power Query to import currency exchanges rates

Power Query - Import Data from the Web - Excel Off The Grid

The web is a vast place with so much information. However, just because the data is there does not make it easy to get at In this post, we'll look

exceloffthegrid.com

Hope this will help

 

[hernantorres23]

Hi, other option

Sub TableExample()
'by Jonny'
Dim IE As Object
Dim doc As Object
Dim strURL As String

strURL = "https://www.banxico.org.mx/tipcamb/main.do?page=tip&idioma=sp"

Set IE = CreateObject("InternetExplorer.Application")
With IE
     '.Visible = True

    .navigate strURL
    Do Until .readyState = 4: DoEvents: Loop
        Do While .Busy: DoEvents: Loop
            Set doc = IE.document
            GetAllTables doc

            .Quit
        End With
End Sub

Sub GetAllTables(doc As Object)

     ' get all the tables from a webpage document, doc, and put them in a new worksheet

    Dim ws As Worksheet
    Dim rng As Range
    Dim tbl As Object
    Dim rw As Object
    Dim cl As Object
    Dim tabno As Long
    Dim nextrow As Long
    Dim I As Long

    Set ws = ActiveSheet

    For Each tbl In doc.getElementsByTagName("TABLE")
        tabno = tabno + 1
        nextrow = nextrow + 1
        Set rng = ws.Range("B" & nextrow)
        rng.Offset(, -1) = "Table " & tabno
        For Each rw In tbl.Rows
            For Each cl In rw.Cells
                rng.Value = cl.outerText
                Set rng = rng.Offset(, 1)
                I = I + 1
            Next cl
            nextrow = nextrow + 1
            Set rng = rng.Offset(1, -I)
            I = 0
        Next rw
    Next tbl

    ws.Cells.ClearFormats

End Sub