> SmartCash is a community governance, cooperation & growth focused blockchain based currency & a decentralized economy.> 

They have a public API, which enables data analysis.  The aim of this analysis is to explore the JSON data available from the API call 
https://explorer3.smartcash.cc/ext/getlasttxs/10/100

I also wanted to get a quick comparision of the last 100 transaction on the Smartcash blockchain against the last 100 transaction on the Steemit blockchain

## Extracting SmartCash JSON data
I have connected to the API call above using Power BI. Using the query editor the following M code can be used



     let
    Source = Json.Document(Web.Contents("https://explorer3.smartcash.cc/ext/getlasttxs/10/100")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"_id", "txid", 
      "blockhash", "__v", "blockindex", "timestamp", "total", "vout", "vin"}, {"_id", "txid", "blockhash", "__v", 
       "blockindex", "timestamp", "total", "vout", "vin"}),
      In     #"Expanded Value1"



   A sample of the returned data can be seen in the image below

![4.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1514938096/lpi7asejsjipbbaw2orh.png)

  
From here we can see that the vout and vin are in lists. To expand this the code can now be updated to 


    let
    Source = 
    Json.Document(Web.Contents("https://explorer3.smartcash.cc/ext/getlasttxs/10/1000")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"_id", "txid", 
     "blockhash", "__v", "blockindex", "timestamp", "total", "vout", "vin"}, {"_id", "txid", "blockhash", "__v", 
     "blockindex", "timestamp", "total", "vout", "vin"}),
    #"Expanded vout" = Table.ExpandListColumn(#"Expanded Value1", "vout"),
    #"Expanded vout1" = Table.ExpandRecordColumn(#"Expanded vout", "vout", {"addresses", 
     "amount"}, {"addresses", "amount"}),
    #"Expanded addresses" = Table.ExpandListColumn(#"Expanded vout1", "addresses"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded addresses",{{"addresses", "Sending 
     address"}}),
    #"Expanded vin" = Table.ExpandListColumn(#"Renamed Columns", "vin"),
    #"Expanded vin1" = Table.ExpandRecordColumn(#"Expanded vin", "vin", {"addresses", "amount"}, 
     {"addresses.1", "amount.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded vin1",{{"addresses.1", "rec. address"}, 
     {"amount", "sent amt"}, {"amount.1", "rec amt"}}),
      in
      #"Renamed Columns1"

In this part of the code we have expanded and also renamed the new address columns to ‘sending address’ and ‘rec address’

A sample of the expanded lists can been seen in the image below

![5.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1514938157/qnrhvflyxkkquetzduib.png)

 
There were still some transformations required.  The amounts shown in the data are in satoshis.  I would like to report in Bitcoin value.  Also each side of the transaction is now shown, the sending and receiving.  That means that the total value will be repeated based on the number of lines in the transaction. Therefore we also need to remove duplicate rows in the transactionid column

The m code can now be amended to the following


      let
    Source = 
      Json.Document(Web.Contents("https://explorer3.smartcash.cc/ext/getlasttxs/10/1000")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"_id", "txid", 
      "blockhash", "__v", "blockindex", "timestamp", "total", "vout", "vin"}, {"_id", "txid", "blockhash", "__v", 
      "blockindex", "timestamp", "total", "vout", "vin"}),
      #"Expanded vout" = Table.ExpandListColumn(#"Expanded Value1", "vout"),
      #"Expanded vout1" = Table.ExpandRecordColumn(#"Expanded vout", "vout", {"addresses", 
       "amount"}, {"addresses", "amount"}),
         #"Expanded addresses" = Table.ExpandListColumn(#"Expanded vout1", "addresses"),
      #"Renamed Columns" = Table.RenameColumns(#"Expanded addresses",{{"addresses", "Sending 
     address"}}),
        #"Expanded vin" = Table.ExpandListColumn(#"Renamed Columns", "vin"),
    #"Expanded vin1" = Table.ExpandRecordColumn(#"Expanded vin", "vin", {"addresses", "amount"}, 
     {"addresses.1", "amount.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded vin1",{{"addresses.1", "rec. address"}, 
      {"amount", "sent amt"}, {"amount.1", "rec amt"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "total BTC value", each 
      [total]/100000000),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"txid"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"total", type number}, 
      {"total BTC value", type number}})
       in
      #"Changed Type"

After loading the data into the model, I then used DAX to carry out further calculations.

## Analyzing the Last 100 Smartcash Transactions

The data was extracted at 23:13 GMT 02/01/18

![6.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1514938259/dc4apy0bazhz0gfzunpg.png)

 
 The last 100 transaction on the Smartcash blockchain amounted to 3.44 Bitcoin.  69 accounts were involved in sending smartcash to 54 receiving accounts.

82% of the value in these 100 transaction was transferred to Coinbase, which is a well know crypto exchange.  57% of the value transferred to coinbase was transferred from the 1 sending address.  This same sending address was responsible for 47% of the overall value transferred.

## The last 100 Steemit Transactions

Steemit blockchain is rather different and the comparable data would be the transferes table in the Steemsql database.

### Bitcoin Exchange rates

Exchange rates for Steem and SBD to Bitcoin were taken at 23.30 GMT from http://www.steemdollar.com/

The following M code was used to extract data from the Steemsql database


     let
    Source = Sql.Database("sql.steemsql.com", "DBSteem", [Query="SELECT *   #(lf)             FROM 
    TxTransfers (NOLOCK)#(lf) WHERE timestamp >= CONVERT(datetime,'01/01/2018') #(lf)             
      and         timestamp< CONVERT(datetime,'01/03/2018')#(lf)"]),
    #"Kept Last Rows" = Table.LastN(Source, 100)
     in
    #"Kept Last Rows"

![7.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1514938339/pvdddkh3xzvxzru1huo2.png)

 
The last 100 transfers on the STEEMIT blockchain amounted to 34.05 Bitcoin.  63 accounts were involved in sending smartcash to 51 receiving accounts.

99.6% of the value in these 100 transaction were transferred to Bittrex, blocktrades and poloniex, which are well know crypto exchanges.  95% of the value transferred to bittrex was transferred from @rainman.  This same sending address was responsible for 95% of the overall value transferred.

## Conclusion

The Smartcash API allows you gather and analyse data from the smartcash blockchain.  In addition to the API above, there are also other APIs for different data.

It is very interesting to see that the value of bitcoin in the last 100 transactions is 9.9 time greater on Steemit than on Smartcash.

The current price of Steem is bitcoin 0.00045
And Smart is 0.00002923
The price of STEEM is therefore 15.3 times greater than Smartcash

It is also interesting to see that 99.6% of the last 100 Steem transfers are to exchanges while this is only 82% on Smartcash

I am part of a Blockchain Business Intelligence community. We all post under the tag #BlockchainBI. If you have an analysis you would like carried out on Steemit or Blockchain data, please do contact me or any of the#BlockchainBI team and we will do our best to help you...

You can find #BlockchainBI on discord https://discordapp.com/invite/JN7Yv7j


<br /><hr/><em>Posted on <a href="https://utopian.io/utopian-io/@paulag/exploring-the-smartcash-api-with-steemit-blockchain-transfer-comparison">Utopian.io -  Rewarding Open Source Contributors</a></em><hr/>