[STEEMSQL.COM] How to create a Steem analytic report with Microsoft Excel

in steemit •  2 years ago

This step by step tutorial will show how to use Microsoft Excel to retrieve data from SteemSQL.com SQL server database and present your result in a simple but nice pivot chart .

Let’s say we want to look how transactions count per month evolves since Steem blockchain has been created.

Writing a SQL query

An efficient SQL query to get such information will be:

    YEAR(Blocks.timestamp) AS [Year],
    MONTH(Blocks.timestamp) AS [Month],
    DAY(Blocks.timestamp) AS [Day],
    COUNT(*) AS [Transactions]
    INNER JOIN Blocks ON Transactions.block_num = Blocks.block_num

As of today, you will get 174 rows like

That's a bunch of rows and hard to visualize data evolution.

Excel to the rescue

Let’s create a nice chart for this with Microsoft Excel

Start Excel, go to the Data tab and select SQL Server as your datasource

Enter the connection information is the wizard dialog box as shown

Select any table (it doesn’t matter at this stage) and click Finish to close the wizard

Click on properties

Select the definition tab

Check the Save password checkbox to avoid Excel asking you for credentials each time you connect to SteemSQL

You can safely ignore the warning as the user and password are public

Open the dropdown control and select SQL

Paste your SQL query and click OK

Select the type of data presentation you want in Excel.

Finalizing the presentation

OK, we got all our data. Let’s make a nice chart.

Organize your data fields in the pivot properties

Tadaa ! You have created your first analytic report using Steem blockchain data!


If you need help, have any comment or request, join steemsql channel on steemit.chat

You like this tutorial, please upvote and follow me for more advanced used of SteemSQL.

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:  

I'm trying to use Java for the connection to the database it it wont connect. It says database not responding!

Hi, top! Question: Can you extract the historical steem_per_mvests? I would use it to make calculations

I am onboard for this. Cool tutorial



Leaving a comment since i cant seem to find the resteem button

I am glad I found this. A big thank you to you. I will study it and I hope to be able to use it.

Thank you for these posts @arcange. I am sure your bots will find this message ;) I just started python and sql with a bit of html experience, because of steemit, I want to dig deep into data, and I am trying your codes etc...I am learning while doing. Thanks again.


Glad you have fun digging into data ;)


got my gloves and shovel. Do you know which table.row contains steempower total?

These blogs are well set out and still relevant. Thanks!


Steen Power is not stored in the database because it is a dynamic value that has to be computed/reevaluated each time


Arghhh. Do you know if there is a place i can scrape recent SP from?

Its strange, because my Steem in the Steem Power Wallet is static until i power-up, or collect a reward. Do you know where this number is held if not in the DB?

Thanks for your time!

Thanks a lot for the tutorial. I look forward to apply it.


Enjoy :)

Very cool idea for a service! Having lots of different available data sources & formats like this will speed up cool developments and experimentation.

This guide is also really well presented and written, thanks for doing this @arcange!
edited: Saw the other post


Spent a lot of time to make it as SteemSQL for dummies :P
Thanks for your positive feedback !


It's really a great post. I didn't know that this stuff was doable from excel, and I'll certainly be experimenting.

Reblogged so more folks can see your awesome-level in action.


He he, sharing and spreading knowledge. Good move.

ありがとうございました。Nice work!!!

wow ,, your writing is very helpful to me, i am very layman with sql server .. thanks. I wait every article from you, hopefully my science in this field is increasing

I tried to login SteemSQL, but ' ID/PW: steemit ' isn't working, Is it changed??


Check SteemSQL website: https://steemsql.com


@callmewill, please stop downloading the full contents of tables!
This has a negative impact on the server performances and bandwidth and penalize all SteemSQL users.


oh, really? sorry for that. I just tried to download a few contents and figured out how can i do this..

Is there a steemsql server on discord?


Support for SteemSQL is provided on https://steem.chat/channel/steemsql