Bitcoin Forum
July 28, 2017, 11:11:29 AM *
News: BIP91 seems stable: there's probably only slightly increased risk of confirmations disappearing. You should still prepare for Aug 1.
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: Getting market's API (Ticker) data into Excel 2007 without Copy and Paste?  (Read 3275 times)
SteveS
Newbie
*
Offline Offline

Activity: 4


View Profile
October 30, 2011, 10:06:27 PM
 #1

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!
1501240289
Hero Member
*
Offline Offline

Posts: 1501240289

View Profile Personal Message (Offline)

Ignore
1501240289
Reply with quote  #2

1501240289
Report to moderator
1501240289
Hero Member
*
Offline Offline

Posts: 1501240289

View Profile Personal Message (Offline)

Ignore
1501240289
Reply with quote  #2

1501240289
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1501240289
Hero Member
*
Offline Offline

Posts: 1501240289

View Profile Personal Message (Offline)

Ignore
1501240289
Reply with quote  #2

1501240289
Report to moderator
1501240289
Hero Member
*
Offline Offline

Posts: 1501240289

View Profile Personal Message (Offline)

Ignore
1501240289
Reply with quote  #2

1501240289
Report to moderator
VeeMiner
Hero Member
*****
Offline Offline

Activity: 751


bitcoin hodler


View Profile
March 19, 2013, 01:30:36 PM
 #2

did you make it work somehow? I'm wondering the same thing
takinbo
Newbie
*
Offline Offline

Activity: 14


View Profile
March 19, 2013, 01:56:19 PM
 #3

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

Bitrated user: takinbo.
David Rabahy
Hero Member
*****
Offline Offline

Activity: 701



View Profile
April 26, 2013, 04:18:38 PM
 #4

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
AlienBoy
Newbie
*
Offline Offline

Activity: 5


View Profile
February 21, 2014, 07:02:42 AM
 #5

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...
testerman
Hero Member
*****
Offline Offline

Activity: 840


View Profile
September 19, 2014, 03:28:11 PM
 #6

yeah i am looking forward to it too

*** THIS AREA IS NOT FOR SALE ***
FeelTheBeat
Hero Member
*****
Offline Offline

Activity: 532


Blocklancer - Freelance on the Blockchain


View Profile
September 20, 2014, 10:47:10 AM
 #7

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.

██
██
██
██
██
██
██
██
The first Decentralized Autonomous Job market (DAJ)
★★ VISIT US ! ★★ JOIN BOUNTY ★★
██
██
██
██
██
██
██
██
Freelance on the Blockchain

amacar
Hero Member
*****
Offline Offline

Activity: 546


View Profile
September 20, 2014, 07:04:43 PM
 #8

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

Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!