VBA code to get data from web

[bhinangt]

i want ONLY buy and sell price in excel from https://coins.co.th/....
Is there any VBA code that can help?

I tried Data>Import from Web menu but that gets full web site content which takes too long time to load...

Thanks in advance

 

[Domenic]

First, set a reference (VBE > Tools > References) to the following libraries...

1) Microsoft XML, v6.0 (or whatever version you have)

2) Microsoft HTML Object Library

Then try...

[COLOR=darkblue]Sub[/COLOR] GetBitcoinBuyAndSellPrice()

    [COLOR=darkblue]Dim[/COLOR] XMLReq [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSXML2.XMLHTTP60
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] sBuySell [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    XMLReq.Open "GET", "https://coins.co.th/", [COLOR=darkblue]False[/COLOR]
    XMLReq.send
    
    [COLOR=darkblue]If[/COLOR] XMLReq.Status <> 200 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Error " & XMLReq.Status & ":  " & XMLReq.statusText
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    HTMLDoc.body.innerHTML = XMLReq.responseText
    
    sBuySell = HTMLDoc.getElementById("quote_string").innerText
    
    MsgBox sBuySell, vbInformation
    
    [COLOR=darkblue]Set[/COLOR] XMLReq = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To split the buy and sell price, you can add the following lines of code...

    [COLOR=darkblue]Dim[/COLOR] sBuy [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sSell [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    sBuy = Trim(Split(sBuySell, "|")(0))
    sSell = Trim(Split(sBuySell, "|")(1))

Hope this helps!

 

[bhinangt]

Thanks genius

First, set a reference (VBE > Tools > References) to the following libraries...

1) Microsoft XML, v6.0 (or whatever version you have)

2) Microsoft HTML Object Library

Then try...

[COLOR=darkblue]Sub[/COLOR] GetBitcoinBuyAndSellPrice()

    [COLOR=darkblue]Dim[/COLOR] XMLReq [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSXML2.XMLHTTP60
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] sBuySell [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    XMLReq.Open "GET", "https://coins.co.th/", [COLOR=darkblue]False[/COLOR]
    XMLReq.send
    
    [COLOR=darkblue]If[/COLOR] XMLReq.Status <> 200 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Error " & XMLReq.Status & ":  " & XMLReq.statusText
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    HTMLDoc.body.innerHTML = XMLReq.responseText
    
    sBuySell = HTMLDoc.getElementById("quote_string").innerText
    
    MsgBox sBuySell, vbInformation
    
    [COLOR=darkblue]Set[/COLOR] XMLReq = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To split the buy and sell price, you can add the following lines of code...

    [COLOR=darkblue]Dim[/COLOR] sBuy [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sSell [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    sBuy = Trim(Split(sBuySell, "|")(0))
    sSell = Trim(Split(sBuySell, "|")(1))

Hope this helps!

 

[Domenic]

You're very welcome!

Cheers!

 

[bhinangt]

You're very welcome!

Cheers!

Just need little extra input...

Because the price keep on changing whenever i run macro again, it must fetch new prices.. But in this case it shows the price of first time.. May be i need macro to clear old values and fetch new values....


Also can you suggest how this changing values can be recorded sequentially with time stamp every 1 minute??

 

[Sektor]

To record, you need real database - not Excel.