Excel SQL Steem Delegation Browser 20171208

in #delegation7 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!

    Sort:  

    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.