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

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.