Results 1 to 2 of 2

Macro: Retrieve value from website

Threaded View

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    13

    Macro: Retrieve value from website

    Good day

    A few months ago I used excel to keep track of some share prices for me. Basically I used a macro to get the value of a certain stock from the following website: http://www.sharenet.co.za/v3/sharesf...sname=&scode=x

    I have a list of shares in excel I keep track of and macro would simply retrieve the value from the website. Next to the share code I have a the formula: =GetValue()

    EXAMPLE:

    A (Code) B (Price)

    1 ANGSBE =GetValue(A1,"Sale")
    2 GFISBO =GetValue(A2,"Sale")
    .
    .
    .

    This method doesn't seem to work anymore for whatever reason. I was wondering if perhaps it might be a setting I changed? Someone from mr.excel was kind enough to write me the macro code a few months ago as I unfortunately have very little knowledge about macros. All I can think is that perhaps some of my security settings in excel changed or maybe the website URL has changed? I tried playing around with it a bit, but couldn't figure it out.

    The macro I used is as follows:

    Function GetValue(sCode As String, sLabel As String) As Long
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://www.sharenet.co.za/v3/quickshare.php?scode=" & sCode
        .send
        Do
            DoEvents
        Loop Until .readyState = 4
        GetValue = CLng(Val(Split(Split(.responseText, "<td class=""textCell"">" & sLabel & "</td>")(1), "<td class=""dataCell"" align=""right"">")(1)))
        .abort
    End With
    
    
    End Function
    
    Sub t()
    MsgBox GetValue("ASA", "Sale")
    End Sub
    ASA is just another sharecode. Can someone please help?

    Thanks!
    Last edited by JBeaucaire; 03-15-2013 at 02:44 PM. Reason: Added code tags, as per forum rules. Don't forget!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1