Bitcoin Forum
December 09, 2016, 01:49:10 PM *
News: Latest stable version of Bitcoin Core: 0.13.1  [Torrent].
 
   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 2768 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!
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1481291350
Hero Member
*
Offline Offline

Posts: 1481291350

View Profile Personal Message (Offline)

Ignore
1481291350
Reply with quote  #2

1481291350
Report to moderator
1481291350
Hero Member
*
Offline Offline

Posts: 1481291350

View Profile Personal Message (Offline)

Ignore
1481291350
Reply with quote  #2

1481291350
Report to moderator
1481291350
Hero Member
*
Offline Offline

Posts: 1481291350

View Profile Personal Message (Offline)

Ignore
1481291350
Reply with quote  #2

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

Activity: 749


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: 11


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: 603



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: 728


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: 490



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.

««««««
««««
««
BetaTransfer.Net 
»»»»»»
»»»»
»»
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!