Bitcoin Forum

Other => Beginners & Help => Topic started by: SteveS on October 30, 2011, 10:06:27 PM


Title: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: SteveS on October 30, 2011, 10:06:27 PM
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!

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: VeeMiner on March 19, 2013, 01:30:36 PM
did you make it work somehow? I'm wondering the same thing

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: takinbo on March 19, 2013, 01:56:19 PM
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

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: David Rabahy on April 26, 2013, 04:18:38 PM
In Excel; Data->From Web->Address (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

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: AlienBoy on February 21, 2014, 07:02:42 AM
Quote from: David Rabahy on April 26, 2013, 04:18:38 PM
In Excel; Data->From Web->Address (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...

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: testerman on September 19, 2014, 03:28:11 PM
yeah i am looking forward to it too

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: FeelTheBeat on September 20, 2014, 10: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.

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: amacar on September 20, 2014, 07: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 = "<!DOCTYPE html><head><script>" & _
    "Object.prototype.getItem=function( key ) { return this[key] }; " & _
    "Object.prototype.setItem=function( key, value ) { this[key]=value }; " & _
    "Object.prototype.getKeys=function( dummy ) { keys=[]; for (var key in this) if (typeof(this[key]) !== 'function') keys.push(key); return keys; }; " & _
    "window.onload = function() { " & _
    "document.body.parse = function(json) { return JSON.parse(json); }; " & _
    "document.body.stringify = function(obj, space) { return JSON.stringify(obj, null, space); }" & _
    "}" & _
    "</script></head><html><body id='JSONElem'></body></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


Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: niccolo_21 on May 01, 2018, 03:09:23 PM
Quote from: amacar on September 20, 2014, 07: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 = "<!DOCTYPE html><head><script>" & _
    "Object.prototype.getItem=function( key ) { return this[key] }; " & _
    "Object.prototype.setItem=function( key, value ) { this[key]=value }; " & _
    "Object.prototype.getKeys=function( dummy ) { keys=[]; for (var key in this) if (typeof(this[key]) !== 'function') keys.push(key); return keys; }; " & _
    "window.onload = function() { " & _
    "document.body.parse = function(json) { return JSON.parse(json); }; " & _
    "document.body.stringify = function(obj, space) { return JSON.stringify(obj, null, space); }" & _
    "}" & _
    "</script></head><html><body id='JSONElem'></body></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, 10: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+

Title: Re: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?
Post by: HabBear on May 01, 2018, 07:37:39 PM
Quote from: SteveS on October 30, 2011, 10: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 (https://chrome.google.com/webstore/detail/cryptofinance/bhjnahcnhemcnnenhgbmmdapapblnlcn?hl=en).

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.


Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines