Website data scraping

[DRSteele]

Can someone please help me?

I am trying to use PQ to get the data from this website into Excel. There are no Tables shown in the navigator.

 

[MCLIFTO8]

If you have access to Power BI you can enable ‘New web table inference’ inside Options>Preview features.

If you don't, then pulling data from the page is going to require using VBA to make a request to the page yourself and navigating the DOM.

 

[smozgur]

@DRSteele: The actual URL is https://www.tdcanadatrust.com/easyweb5/home/tdtoday.html.

If you need the "US Exchange Rate" box, then it is fetched by JavaScript after the page load and not included to the initial HTML page, so you can't catch it by using VBA. However, the called web service returns the data in JSON format. So, it can be used in Power Query. A very simple implementation could be:

let
    Source = Json.Document(Web.Contents("https://www.tdcanadatrust.com/includes/rates/fxNonCashRatesJSON.jsp?get_param=value")),
    rates = Source[rates],
    ConvertedToTable = Table.FromList(rates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"product", "rate"}, {"Column1.product", "Column1.rate"}),
    ExpandedColumn1product = Table.ExpandRecordColumn(ExpandedColumn1, "Column1.product", {"code", "limit", "unit"}, {"Column1.product.code", "Column1.product.limit", "Column1.product.unit"}),
    ExpandedColumn1rate = Table.ExpandRecordColumn(ExpandedColumn1product, "Column1.rate", {"cad", "usd"}, {"Column1.rate.cad", "Column1.rate.usd"}),
    ExpandedColumn1ratecad = Table.ExpandRecordColumn(ExpandedColumn1rate, "Column1.rate.cad", {"rec", "pay"}, {"Column1.rate.cad.rec", "Column1.rate.cad.pay"}),
    ExpandedColumn1rateusd = Table.ExpandRecordColumn(ExpandedColumn1ratecad, "Column1.rate.usd", {"rec", "pay"}, {"Column1.rate.usd.rec", "Column1.rate.usd.pay"}),
    Result = ExpandedColumn1rateusd
in
    Result

If you need the "Prime Rates" then it could be retrieved by using VBA. Again, very simple implementation could be:

Sub getWebData()
' Requires Microsoft XML Reference (Tool->References->Microsoft XML v6.0)
' Requires Microsoft HTML Object Library (Tool->References->Microsoft HTML Object Library)
Dim xmlHttp As New MSXML2.XMLHTTP60
Dim html As HTMLDocument
Dim strUrl As String
Dim pContainer As HTMLDivElement
Dim lstPrime As HTMLUListElement

  With xmlHttp
    strUrl = "https://www.tdcanadatrust.com/easyweb5/home/tdtoday.html"
    .Open "GET", strUrl, False
    .send
    If .Status = 200 Then
      Set html = New HTMLDocument
      html.body.innerHTML = .responseText
      Set pContainer = html.getElementById("pContainer")
      Set lstPrime = pContainer.getElementsByClassName("td-callout-content")(0).getElementsByTagName("ul")(0)
      MsgBox lstPrime.innerText
    End If
  End With
End Sub

Both method assumes the same data structure to keep working. It is unlikely for the JSON document, but if they change a class name or element structure for the Prime Rates box, then the VBA code will fail.

 

[Automatrix]

You may be able to scrape it with Power Automate, if you have access to that.

 

[DRSteele]

MCLIFTO8, Thanks for the reply. I can't use VBA for this project, and I do not have Power BI.

 

[DRSteele]

Automatrix, Thanks for the reply. I can't use Power Automate for this project. I have to be able to give this Excel sheet to others, and they know even less than I do about these special tools.

 

[DRSteele]

Smozgur. Thanks for your reply. That works! I will just continue to remove columns in the query because all I want is the CDA/usd exchange rate.

I have no idea what's going on here regarding JSON and HTML. There are so many websites that I try to connect to with PQ that also lack tables. I assumed it was because of JSON (whatever that is!).

Try this, for example - I want to get the NHL standings into a spreadsheet using PQ. Is there a similar way to do that?

 

[jdellasala]

Hello. I'm just wondering if the relatively new Currency Data Type can't help here since you appear to have 365. Here's a quick screenshot of what it looks like and what data it can provide:

Also, I mentioned in a previous post an Add-In called FRED. It is found in Microsoft's Add-in list or can be downloaded from the link provided. It allows for downloading of all kinds of economic data.
There's also and Add-in called EIA-FRED, from the Department of Energy. Note - it is not properly signed and will give a security warning if it's loaded. It provides a LOT of information, but the fact that I haven't been able to get anyone to find me the update it says is available means I don't use it a lot - but if you want to get the data easily, it certainly does the job.

No VBA required for any of the above, and the data pulled in is static, hard coded without the Add-in.

 

[DRSteele]

Thanks JD. The currency quote in Data Type is the spot price, which no bank customer can get since banks pay a different rate when they buy forex from what the accept to sell forex. So what I need is a transaction quote from a specific bank to actually buy and sell CDA to USD. See, TD will buy my USD with 1.33 CDA but will sell me USD for 1.40 CDA. That quote spread is super for banks, eh?

 

[smozgur]

Smozgur. Thanks for your reply. That works! I will just continue to remove columns in the query because all I want is the CDA/usd exchange rate.

I have no idea what's going on here regarding JSON and HTML. There are so many websites that I try to connect to with PQ that also lack tables. I assumed it was because of JSON (whatever that is!).

Try this, for example - I want to get the NHL standings into a spreadsheet using PQ. Is there a similar way to do that?

Sure. I just retrieved the whole table since I didn't know what you exactly need, or perhaps you might need more info. If you just need a single value from that big table and having difficulty setting it up then just let me know the column and row positions (product code and column name), so I can shorten the code.

I'll check the other link. I am not sure if it works with XHR/Fetch or not.