Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?

HabBear

hero member

Activity: 1106

Merit: 638

Quote from: SteveS on October 30, 2011, 05:06:27 PM

I've searched around the forum for some insight into this, but I have not found anything relevant to importing ticker data without Cut and Paste.

Thanks for any suggestions!

Google Sheets has one - and it's incredibly easier than Excel and faster than writing JSON code.

It's the Google Cryptofinance add on.

Quote from: https://jbuty.com/how-to-get-crypto-currencies-rates-and-more-in-google-sheet-1a57e571bc14

The CRYPTOFINANCE() function, available as a Google Sheets Add-On, uses the coinmarketcap API to returns the rates, market cap, volume, change, total supply and rank.

niccolo_21

jr. member

Activity: 54

Merit: 1

Quote from: amacar on September 20, 2014, 02:04:43 PM

I checked and it is working ok.

Vba code copied from macro

Code:

Option Explicit
Public JSON As Object
Private ie As Object

Sub test()

Call initJson

    Dim jsObj As Object
    Dim jsArray As Object

    

Dim x As Long, y As Long
    Dim htm As Object

    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "https://www.bitstamp.net/api/ticker/", False
        .send
        htm.body.innerhtml = .responsetext
    End With
    
    Set jsObj = JSON.Parse(htm.body.innerhtml)
    Cells(1, 2) = jsObj.getItem("high")
    Cells(2, 2) = jsObj.getItem("last")
    Cells(3, 2) = jsObj.getItem("timestamp")


End Sub




Public Sub initJson()
    Dim html As String

    html = ""

    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .navigate "about:blank"
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        .Visible = False
        .document.Write html
        .document.Close
    End With

    ' This is the body element, we call it JSON:)
    Set JSON = ie.document.getElementById("JSONElem")

End Sub

Public Function closeJSON()
    ie.Quit
End Function

Quote from: FeelTheBeat on September 20, 2014, 05:47:10 AM

What exactely you need?
With macro I can get for example : https://www.bitstamp.net/api/ticker/ or other api. Only what you need is IE8 or higher.

Here is example
For refreshing data just click on button refresh.
Excel file: https://www.dropbox.com/s/u44xev0wvwjgmyk/Zvezek1.xlsm?dl=0

If you need further help just say and I will try to help you.

could it be possible to have the dropbox file back on line?
I checked to and unfortunately it's not working ok for me :/

the problem with excel 2007 is that you can't install power query, and at the moment I would prefer not switching to excel 2010+

amacar

hero member

Activity: 546

Merit: 500

I checked and it is working ok.

Vba code copied from macro

Code:

Option Explicit
Public JSON As Object
Private ie As Object

Sub test()

Call initJson

    Dim jsObj As Object
    Dim jsArray As Object

    

Dim x As Long, y As Long
    Dim htm As Object

    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "https://www.bitstamp.net/api/ticker/", False
        .send
        htm.body.innerhtml = .responsetext
    End With
    
    Set jsObj = JSON.Parse(htm.body.innerhtml)
    Cells(1, 2) = jsObj.getItem("high")
    Cells(2, 2) = jsObj.getItem("last")
    Cells(3, 2) = jsObj.getItem("timestamp")


End Sub




Public Sub initJson()
    Dim html As String

    html = ""

    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .navigate "about:blank"
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        .Visible = False
        .document.Write html
        .document.Close
    End With

    ' This is the body element, we call it JSON:)
    Set JSON = ie.document.getElementById("JSONElem")

End Sub

Public Function closeJSON()
    ie.Quit
End Function

FeelTheBeat

hero member

Activity: 700

Merit: 500

What exactely you need?
With macro I can get for example : https://www.bitstamp.net/api/ticker/ or other api. Only what you need is IE8 or higher.

Here is example
For refreshing data just click on button refresh.
Excel file: https://www.dropbox.com/s/u44xev0wvwjgmyk/Zvezek1.xlsm?dl=0

If you need further help just say and I will try to help you.

testerman

hero member

Activity: 840

Merit: 1000

yeah i am looking forward to it too

AlienBoy

newbie

Activity: 5

Merit: 0

Quote from: David Rabahy on April 26, 2013, 11:18:38 AM

In Excel; Data->From Web->Address (Go">http://bitcoincharts.com/)->Go
if Script Error popup comes up then click No
click black right-pointing arrow in yellow box to select the Market table
click Import
click OK

to manually refresh; Data->Refresh

to automatically refresh periodically; right click on table cell->Data Range Properties->Refresh every
enter number of minutes (I use 1)->OK

I know this method to get data from a web page, thanks. But it will be nice to know how to import data from JSON into Excel, in that way we can have wider range of application!

Sorry to revive an old thread, is there anyone knows how to import API JSON data into Excel? I searched many places but all in vain...

David Rabahy

hero member

Activity: 709

Merit: 503

In Excel; Data->From Web->Address (Go">http://bitcoincharts.com/)->Go
if Script Error popup comes up then click No
click black right-pointing arrow in yellow box to select the Market table
click Import
click OK

to manually refresh; Data->Refresh

to automatically refresh periodically; right click on table cell->Data Range Properties->Refresh every
enter number of minutes (I use 1)->OK

takinbo

newbie

Activity: 18

Merit: 0

From my little research, it doesn't appear that Excel 2007 can parse and interpret JSON. A developer could build a service to translate the data from MtGox, etc to a format that Excel 2007 can use. How much of a PITA is this to you? How often do you need to retrieve data from MtGox and others?

For reference purposes, you can read about the Excel feature here: http://office.microsoft.com/en-001/excel-help/get-external-data-from-a-web-page-HA010218472.aspx

VeeMiner

hero member

Activity: 752

Merit: 500

bitcoin hodler

did you make it work somehow? I'm wondering the same thing

SteveS

newbie

Activity: 4

Merit: 0

Anybody had any luck importing, into Excel 2007 but without resorting to Cut and Paste, the data that MtGox, CampBX or Tradehill make available by using their API's?

Excel 2007 has supposedly has the ability to "Get External Data" "From Web", but it's beaten me into submission.

API's:
http://mtgox.com/api/0/data/ticker.php
http://CampBX.com/api/xticker.php
https://api.tradehill.com/APIv1/USD/Ticker

The problem seems to be that Excel doesn't understand JSON, but when I look at the output from those API sites, it looks like text that I should be able to parse without much trouble.

I've searched around the forum for some insight into this, but I have not found anything relevant to importing ticker data without Cut and Paste.

Thanks for any suggestions!

Topic: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste? (Read 4324 times)