[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:

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

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!

Support

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:  Trending

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

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!

I am onboard for this. Cool tutorial

·

Thanks

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.

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

·

Enjoy :)

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

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.
Thanks!

ありがとうございました。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??