Excel VBA Scraping Cryptocurrencies from Yahoo

I was looking for a solution to demonstrate the concept of scraping data from the internet. Not too familiar with cryptocurrencies and I never knew they had so much to choose from. But the major take away is that you can scrape the page for data and import into Excel. To make this macro work for you, just choose the site and the table on the page and it works right out of the box. Let me know your comments below.

Video Demonstration

Code:

Sub getcrypt()
Dim oDom As Object: Set oDom = CreateObject(“htmlFile”)
Dim x As Long, y As Long
Dim oRow As Object, oCell As Object
Dim data

y = 1: x = 1

With CreateObject(“msxml2.xmlhttp”)
.Open “GET”, “https://finance.yahoo.com/cryptocurrencies”, False
.send
oDom.body.innerHTML = .responseText
End With

With oDom.getElementsByTagName(“table”)(0)
ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
For Each oRow In .Rows
For Each oCell In oRow.Cells
data(x, y) = oCell.innerText
y = y + 1
Next oCell
y = 1
x = x + 1
Next oRow
End With

Sheets(1).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
End Sub

Please follow and like us:

Author: admin2344

Leave a Reply