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?

AUDUSD

USDCAD

USDCHF

EURUSD

GBPUSD

USDJPY

NZDUSD

USDCNH

<tbody>
</tbody>

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.

<tbody>
</tbody>

 

[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