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

in dtube •  3 months ago



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)
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

Congratulations! This post has been upvoted from the communal account, @minnowsupport, by paulag from the Minnow Support Project. It's a witness project run by aggroed, ausbitbank, teamsteem, theprophet0, someguy123, neoxian, followbtcnews/crimsonclad, and netuoso. The goal is to help Steemit grow by supporting Minnows and creating a social network. Please find us in the Peace, Abundance, and Liberty Network (PALnet) Discord Channel. It's a completely public and open space to all members of the Steemit community who voluntarily choose to be there.

Finally we get to see how you do it. I think this post is going to the moon!

·

this post was a nightmare. because I have used Dtube, I was unable to load the images when I created the post on Dtube. This means I then had to edit the post to add the images, but even at that it was not that simple.

·
·

Well it shows the lengths you'll go to do things right. Did you just screenshot them?

I really don't like posts with only video's and no written description. Video's are great, but in the internet age, time matters.

·
·
·

time does matter. yep I screen captured the images as I worked through the exercise

Hello. A neat trick. I didn't know that excel (with addons) can query a web page. I first thought that this tutorial will be about accessing data from steemsql.com.

Nice to know that it could be done in many ways. Thanks!

·

Thank you @ervin-lemark. Excel has awesome powers now. is is the basis for microsofts new package, power bi.

I also access data from steemsql, its an awesome resource. I will be doing some tutorials on it in the future

Really great trick!

Who would have thought Excel has this power also XD

Thanks for sharing :)

After talking with @heimindanger, I made a post with the latest stats of DTube and how well it is competing with Youtube. Do check it out! You are part of the stats @paulag :)

Cheers XD