Print Page - Retrieve my bitcoin data on blockchain.info using Excel VBA

Title: Retrieve my bitcoin data on blockchain.info using Excel VBA
Post by: Latinoz71 on May 08, 2015, 10:37:00 PM

I got a web wallet on blockchain.info. It has several addres.
How can i retrieve my transactions (sent and received), and for every movement some data (date and time, amount, hash, MY NOTE, address RECEIVED...) using their api and my excel VBA sheet?
I imagine reading on internet that i must use some JSON request and read the site's answer, but i don't know how to start. The API for all are described here: https://blockchain.info/it/api/blockchain_api
Can anyone here help me please?
After 2day i'm able to put json string in a Excel-VBA variable. Here's the code:

Code:

Option Explicit

Sub ReadJsonAndParse()
    Dim strURL As String
    Dim xmlHttp As Object
    Dim strReturn As String

    strURL = "https://blockchain.info/it/address/15YMyRVQ1wyLsfwGP9Le97D3BRVG3nMjVW?format=json"

    'Open URL and get JSON data
     Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
     xmlHttp.Open "GET", strURL
     xmlHttp.setRequestHeader "Content-Type", "text/xml"
     xmlHttp.send

     'Save the response to a string
     strReturn = xmlHttp.responseText
End Sub

In the "strReturn" there is the whole retrieved string; i noticed i got in the part called ROOT some data i need: (address, total_received, total_sent and final_balance) and the number of transaction for the address, 3 transaction in total for this address ("n_tx": 3,) numbered 0,1 and 2. Each of this got a "hash" that i want retrieve, but it seems very hard to do. The amount of each transaction is in the array but i don't know how extract.
In the retrieved string i can't find my "NOTE" as i wrote in the page "My Wallet --> Transactions (recent)" near the yellow icon with a green plus (+) into. Where are them?
Tnx in advance at all.

Title: Re: Retrieve my bitcoin data on blockchain.info using Excel VBA
Post by: someguy123 on May 08, 2015, 11:06:01 PM

Quote from: Latinoz71 on May 08, 2015, 10:37:00 PM

In the retrieved string i can't find my "NOTE" as i wrote in the page "My Wallet --> Transactions (recent)" near the yellow icon with a green plus (+) into. Where are them?

I don't think they have an API which includes the "notes". They aren't put into the actual blockchain, they're on BC.INFO's database instead.

Each transaction has inputs and outputs:
If a transaction has an input with your address on it, that means you are SENDING coins
If a transaction has an output with your address on it, that means you are RECEIVING coins

To get total amount:
for amounts sent in a transaction, look for inputs with your address on, total the values of those
for amounts received in a transaction, look for outputs with your address on, total the values of these

Title: Re: Retrieve my bitcoin data on blockchain.info using Excel VBA
Post by: Latinoz71 on May 08, 2015, 11:15:42 PM

Many thanks for the rapid answer. The problem is i don't know how navigate into a json response string using VBA and Excel.

Title: Re: Retrieve my bitcoin data on blockchain.info using Excel VBA
Post by: hexafraction on May 08, 2015, 11:18:59 PM

You could work with ScriptControl and/or JScriptTypeInfo (as JSON is just JavaScript that describes an object):

Code:

Option Explicit

Private ScriptEngine As ScriptControl

Public Sub InitScriptEngine()
    Set ScriptEngine = New ScriptControl
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
    ScriptEngine.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
End Sub

Public Function DecodeJsonString(ByVal JsonString As String)
    Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
End Function

Public Function GetProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Variant
    GetProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function

Public Function GetObjectProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Object
    Set GetObjectProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function

Public Function GetKeys(ByVal JsonObject As Object) As String()
    Dim Length As Integer
    Dim KeysArray() As String
    Dim KeysObject As Object
    Dim Index As Integer
    Dim Key As Variant

    Set KeysObject = ScriptEngine.Run("getKeys", JsonObject)
    Length = GetProperty(KeysObject, "length")
    ReDim KeysArray(Length - 1)
    Index = 0
    For Each Key In KeysObject
        KeysArray(Index) = Key
        Index = Index + 1
    Next
    GetKeys = KeysArray
End Function

Example usage:

Code:

    JsonString = "{""key1"": ""val1"", ""key2"": { ""key3"": ""val3"" } }"
    Set JsonObject = DecodeJsonString(CStr(JsonString))
    Keys = GetKeys(JsonObject)

source (http://stackoverflow.com/a/7300926)

Bitcoin Forum

Economy => Service Discussion => Topic started by: Latinoz71 on May 08, 2015, 10:37:00 PM