Macro to retrieve internet share price

Function getPrice(CompanyCode) As Variant
 
    Dim MyHTTP As New XMLHTTP
    Application.StatusBar = True
    Application.StatusBar = "Obtaining data for " & CompanyCode & " tickers...."
    
    Dim URL As String
    URL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & CompanyCode & "&f=l1"
    MyHTTP.Open "GET", URL, False
    MyHTTP.send
    If MyHTTP.statusText <> "OK" Then GoTo ServerErrorHandler
    
    If Not (WorksheetFunction.IsNumber(MyHTTP.responseText)) Then getPrice = "no data"
    
    getPrice = CDbl(MyHTTP.responseText)
 
End Function
 
ServerErrorHandler:
MsgBox "Could not get data"

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Option Explicit
Public xmlHttpRequest As MSXML2.XMLHTTP
 
 
Function getPrice(CompanyCode As String) As Variant
    If Not xmlHttpRequest Is Nothing Then Set xmlHttpRequest = Nothing
    Dim url As String
    url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & CompanyCode & "&f=l1"
    Dim myHTTP
    Set myHTTP = New XMLHTTP
    myHTTP.Open "GET", url, False
    myHTTP.send ""
    If myHTTP.statusText <> "OK" Then GoTo ServerErrorHandler
    If Not (WorksheetFunction.IsNumber(myHTTP.responseText)) Then getPrice = "no data"
    getPrice = CDbl(myHTTP.responseText)
    
    Exit Function
 
ServerErrorHandler:
    MsgBox "Error. Could not get data for " & CompanyCode
 
End Function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

Sub NetFinder()
Dim rng1 As Range, c As Range
Set rng1 = Range([a2], Cells(Rows.Count, "A").End(xlUp))
Dim oXMLHTTP
Dim HTMLDoc As HTMLDocument, RegEx
Set RegEx = CreateObject("vbscript.regexp")
Set HTMLDoc = New HTMLDocument
With RegEx
.Global = True
.MultiLine = True
End With
Set oXMLHTTP = CreateObject("microsoft.xmlhttp")
For Each c In rng1
   oXMLHTTP.Open "GET", "http://finance.yahoo.com/q?s=" & c.Value, False
   oXMLHTTP.send
   HTMLDoc.body.innerHTML = oXMLHTTP.responseText
   RegEx.Pattern = "[\r\n\s]"
   tmpstr = RegEx.Replace(HTMLDoc.body.innertext, vbNullString)
   RegEx.Pattern = ".*LastTrade:(.+?)Trade.*"
   Cells(c.Row, "H") = RegEx.Replace(tmpstr, "$1")
Next
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

Sub RefreshClick()
    Application.ActiveWorkbook.RefreshAll
End Sub
                                              

1:
2:
3: