SteveS (OP)
Newbie
 Offline
Activity: 4
Merit: 0
|
 |
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.phphttp://CampBX.com/api/xticker.php https://api.tradehill.com/APIv1/USD/TickerThe 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! |
|
|
|
|
|
VeeMiner
|
|
March 19, 2013, 01:30:36 PM |
|
did you make it work somehow? I'm wondering the same thing
|
|
|
|
|
takinbo
Newbie
Offline
Activity: 18
Merit: 0
|
|
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 |
|
|
|
|
|
David Rabahy
|
|
April 26, 2013, 04:18:38 PM |
|
In Excel; Data->From Web->Address ( http://bitcoincharts.com/)->Goif 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
Activity: 5
Merit: 0
|
|
February 21, 2014, 07:02:42 AM |
|
In Excel; Data->From Web->Address ( http://bitcoincharts.com/)->Goif 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
|
|
September 19, 2014, 03:28:11 PM |
|
yeah i am looking forward to it too
|
|
|
|
|
|
FeelTheBeat
|
|
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=0If you need further help just say and I will try to help you. |
|
|
|
|
|
|
365 |
TM | | | |
EZ365 is a digital ecosystem that combines
the best aspects of online gaming, cryptocurrency
trading and blockchain education. ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀ | |
..WHITEPAPER.. ..INVESTOR PITCH..
| | | |
.'M████▀▀██ ██
W█Ws'V██ ██▄▄███▀▀█
i█████m.~M████▀▀██ ███
d███████Ws'V██ ██████
****M██████m.~███f~~__mW█
██▀▀▀████████= Y██▀▀██W ,gm███████
g█████▄▄▄██ █A~`_WW Y█ ██!,████████
g▀▀▀███ ████▀▀`_m████i!████P W███ ██
_███▄▄▄██▀▀▀███Af`_m███ █W ███A ]███ ██
__ ~~~▀▀▀▀▄▄▄█*f_m██████ ██i!██!i███████
Y█████▄▄▄▄__. i██▀▀▀██████████ █!,██████
8█ █▀▀█████.!██ ██████████i! █████
'█ █ █ █W M█▄▄▄██████ ██ !██
!███▄▄█ ██i'██████████ ██
Y███████████.]██████████████
█ ███████b ███ ██████
Y █ █▀▀█i!██ ████
V███ █ █W Y█████
~~▀███▄▄▄█['███
~~*██ | |
Play | | | |
│
│ ███
│ ███
│ ███
│ │ ███
███ │ ███
███ ███ ███
│ ███ ███ ███
███ ███ ███ ███
███ ███ │ │
███ ███ │ │
│ │
│ | |
Trade | | | |
__▄▄████▄▄
__▄▄███████████████▄▄▄
_▄▄█████████▀▀~`,▄████████████▄▄▄
~▀▀████▀▀~`,_▄▄███████████████▀▀▀
d█~ =▀███████████████▀▀
]█! m▄▄ '~▀▀▀████▀▀~~ ,_▄▄
,W█. *████▄▄__ ' __▄▄█████
!██P █████████████████████
W█. - ██████████████████▀
i██[ ~ ▀▀█████████▀▀▀
g███!
Y███ | |
Learn |
|
|
|
|
amacar
|
|
September 20, 2014, 07:04:43 PM |
|
I checked and it is working ok. Vba code copied from macro 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
|
|
|
|
|
niccolo_21
Jr. Member
Offline
Activity: 54
Merit: 1
|
|
May 01, 2018, 03:09:23 PM |
|
I checked and it is working ok. Vba code copied from macro 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
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+ |
|
|
|
|
|
HabBear
|
|
May 01, 2018, 07:37:39 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.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. |
|
|
|
|
|
