How to connect Excel to Steemd User Data - Steemit Business Intelligence

---

CLICK IMAGE TO PLAY VIDEO

There are many ways to get data into Excel and many reasons you would want to pull data into Excel. Today we are going to use Excel to connect to Steemd and extract data for steemit accounts. The process shown can be used to extract and transform data from other websites too.

Want to know how to do this in Power BI? http://theexcelclub.com/power-bi-connecting-to-tables-on-a-website-and-transforming-that-data/

In a recent post I went through the steps in calculating the worth of a vote. You can read the post here. https://steemit.com/bisteemit/@paulag/how-to-calculate-the-worth-of-any-steemit-vote-steemit-business-intelligence
In this calculation we needed both the voters voting power and also the vote’s Effective sp. What you will learn in this post is how to extract this data into excel so that you can easily update it without the need for manual entry into excel.

Connecting to a table on a website using Excel

The website we wish to connect to is https://steemd.com/@paulag

The fields of data that we want are in the table boxed in red. As we can see this table also contains a lot of data we do not want, data we can call noise.
To connect to the datasource in Excel we are going to use the add in Power Query (available for Excel 2010 and 2013) and is a feature of Excel 16 ‘get and transform data’. The example shown uses Excel 2013.
In Excel on the power query ribbon first we will select from web and in the URL we can enter the website that contains the table of data.

Once Excel connects to the website a new window, the navigator window will open. On the left you can see a list of tables that Excel has found on the webpage. By selecting a table on the left, you will see a preview on the right. Once we have found the correct table and ensure that we have ticked it, we can then select edit.

By selecting edit the power query window will now open. The data contains two columns. The first column are attributes and the second column are the value.

Transforming the data in Excel

The first step in transforming this data to a usable format is to make the attributes row headers. The steps involved here are rather simple.

First on the transform ribbon select transpose

This will move all of the data from column 1 to row 1 and all of the data from column 2 to row 2

Next also under the transform ribbon we will select Use First Rows and Headers.

The next step in this transformation is to remove the columns of data that we do not need. To do this, we will select the 3 columns we need to keep. Name, % Voting Power and Effective SP. Once we have these selected, on the home ribbon, select remove columns and remove other columns. We will then be left with the data that we require.

Finally we need to change the data types. Voting power needs to be changed to % and effective sp such be a decimal number. To do this go to the transform ribbon and select the correct data type.

Adding more accounts to the table

Once you have created and transformed your first table of data, it is easy to add more users to this table.
Expand the query tab on the left of the screen. In here you will find the query we have been working on. Right click this query and select duplicate.

You can also rename the query so you don’t get mixed up. At the moment the data in the first and second queries are the same. To quickly change the user open the Advance editor on the home ribbon

This will open the advanced editor window showing the M code used to get and transform the data. In here we will replace the name @paulag with @steemitbc found on the first row of code. Then we will save

Our second table of data now contains the data for @steemitbc.

Appending queries

The final step you will learn today is how to append these tables together. In this example I have only taken two accounts, however you can do the steps above multiple times and append them all together.
To append both of these table together, select the first table from the queries tab. Then under the home ribbon under combine, select append.

This will then open a new window allowing you select which queries you wish to append

This will leave you with a table of data containing both of the queries.

To keep this data up to date you just need to Refresh the data from the home ribbon. Now if you select close and load, this will load your table into excel.

I am part of a Steemit Business Intelligence community. We all post under the tag #BIsteemit. If you have an analysis you would like carried out on Steemit data, please do contact me or any of the #bisteemit team and we will do our best to help you...

you can find #bisteemit on discord https://discordapp.com/invite/JN7Yv7j

Follow, upvote and resteem

---

► Watch on DTube
► Watch Source (IPFS)