Scrape CCY previous closing from marketwatch using VBA

[hkese]

Anyone has any idea how can I use vba to scrape the previous closing rate of a list of currencies to excel?

[TABLE="width: 0"]
<tbody>[TR]
[TD]AUDUSD
[/TD]
[/TR]
[TR]
[TD]USDCAD
[/TD]
[/TR]
[TR]
[TD]USDCHF
[/TD]
[/TR]
[TR]
[TD]EURUSD
[/TD]
[/TR]
[TR]
[TD]GBPUSD
[/TD]
[/TR]
[TR]
[TD]USDJPY
[/TD]
[/TR]
[TR]
[TD]NZDUSD
[/TD]
[/TR]
[TR]
[TD]USDCNH
[/TD]
[/TR]
</tbody>[/TABLE]

For example, if I want to check the previous closing of AUDUSD, I would need to go to the following website, and copy the previous close figure to excel.
But I would need to do the above step for every single currencies. Any way to use vba to scrape such figure?

https://www.marketwatch.com/investing/currency/audusd/historical
https://www.marketwatch.com/investing/currency/usdcad/historical

etc..

Thank you.

[TABLE="width: 0"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Excel 2010
	A	B
1	USDCAD	1.2491
2	audusd	0.7906
3	eurbrl	4.0038
4	mxnpln	0.1805
5	usdeur	0.8080
Sheet5

Cell Formulas
Range	Formula
B1	=Retrieve_Previous_CloseFX(A1)
B2	=Retrieve_Previous_CloseFX(A2)
B3	=Retrieve_Previous_CloseFX(A3)
B4	=Retrieve_Previous_CloseFX(A4)
B5	=Retrieve_Previous_CloseFX(A5)

vba function:

Dim xmlHttpObj As Object
Dim oHTMLDoc As MSHTML.HTMLDocument

Function Retrieve_Previous_CloseFX(ccy As String) As Variant
Dim strURL As String
If xmlHttpObj Is Nothing Then Set xmlHttpObj = CreateObject("MSXML2.XMLHTTP")
If oHTMLDoc Is Nothing Then Set oHTMLDoc = CreateObject("htmlfile")

strURL = "https://www.marketwatch.com/investing/currency/" & ccy & "/historical"
xmlHttpObj.Open "GET", strURL, False
xmlHttpObj.send

oHTMLDoc.body.innerHTML = xmlHttpObj.responseText

Retrieve_Previous_CloseFX = oHTMLDoc.getElementsByClassName("lastcolumn data mrkthours price").Item(0).innerText
End Function

you need to add a referenceto: MICROSOFT HTML OBJECT LIBRARY