Excel SQL Steem Delegation Browser 20171208

in delegation •  7 years ago  (edited)


This is a guide on how to connect an Excel sheet to a Steem Blockchain SQL Server and link Charts and pivot tables to it or create a control Dashboard

Delegating Steem Power is likely to be widely used in the next future so it may become useful a tool for tracking it with the report and data management functionalities of an Excel workbook

In a few steps we will create a tool that gets delegation history and current delegation account state from the blockchain

it is possible to change the SQL query accessing Excel objects acording to the input parameters from a range of cells in Excel

This animations below ,shows a tool to gets delegation history for given delegator and delegatees ,it also filter the historic data to show a snapshot of live delegations in real time


SQLDeleg.gif


You are welcome to give it a try from : https://github.com/Devba/StemmDelegatingWorksheet


Steps for creating a Excel linked to the Steem blockchain


1 - Create a data source connected to the SQL server

W have to create a new data connection in Excel , the steps for this may vary depending on Excel version , but the important is to select an SQL Server type connection

Then enter the following parameters when required

  • Server URL :sql.steemsql.com
  • User : steemit
  • Password. : steemit
  • There is a great tutorial for this step , check it if you are struggling with the connectiion :https://steemit.com/steemit/@arcange/steemsql-com-how-to-create-a-steem-analytic-report-with-microsoft-excel>

    One tip to avoid downloading a whole table which could slow down the queries is to the connection properties dialog and change the following parameters

  • Command type : SQL
  • Commad text : SQL query with limits or filters

  • something like this



    2-Create the user interface

    We can use excel ranges asr input parameters , then we will insert to buttons which we will link to the code below

    We can use excel ranges asr input parameters

    I inserted two button shapes and then set them to run macros

  • Update from blockchain : pulls historic delegation data from server filtering by delegator or delegatee
  • Summarize : shows live delegations (ignoring old delegations which have been updated)


  • The result data feeds a table Excel object , so we can use filtering , ordering, etc.... as well as creating pivot tables and charts


    3-Set parameters to query database on works

    Sorry this section is still on works


    ![filters.gif]()

    4-Change SQL from Excel macros

    This is the code which updates sql to query de database ,its inputs are 2 excel cells

    We just need a few lines to launch the query and update the charts

    Get Excel sample and source code

    Sample Excel available below , please allow Excel to Run Macros in order to use it

    https://github.com/Devba/StemmDelegatingWorksheet


    Any questions , comments on the post or visit https://discord.gg/zvReFS

    On works...

    Thanks for interesting in this tool. I am still updating it ...ing


    Waiting for your feedback using the Excel , Thanks!

    Authors get paid when people like you upvote their post.
    If you enjoyed what you read here, create your account today and start earning FREE STEEM!
    Sort Order:  

    Buen trabajo, luego lo pruebo, gracias ;)

    Good work! Be sure to check out #bisteemit and consider adding that tag.

    Excellent post. Making steemsql more useable to the masses.

    Your publications are really beautiful I've checked most of them and found them amazing you're really worth it all thanks for sharing I hope to be like you in the future I work with all my efforts and perfection of God