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
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
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
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.
The data was extracted at 23:13 GMT 02/01/18
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.
Steemit blockchain is rather different and the comparable data would be the transferes table in the Steemsql database.
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"
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 
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