Exchange Transfer Transparency for 2016, 2017, and 2018
I worked on this yesterday and wanted to share it with the community: https://github.com/lukestokes/steem-exchange-transfers
From the README:
Steem Exchange Transfers
Purpose:
To bring perspective and transparency to the exchange transfer activity of accounts on the Steem blockchain. Some people see the Steem blockchain as a mechanism to extract value. Others see it as a long-term investment in a decentralized, censorship-resistant, immutable blockchain-based communication and data storage platform. The intention of this data is to help inform our perspectives of various accounts on the Steem blockchain.
Description:
steem_exchange_transfers.php is a script which uses data from SteemSQL and historical USD value for SBD and STEEM to calculate how much USD value various accounts have transferred from their accounts to an exchange (a withdrawal from the Steem blockchain represented by a negative number) or transferred from an exchange to their accounts (a deposit into the Steem blockchain represented by a positive number).
Methodology and Process Explanation:
Historical prices for SBD and STEEM were obtained by taking the average of the open and close for a given day based on CMC data. You can find this data in
historical_sbd_price.csvandhistorical_steem_price.csvSTEEM and SBD exchange transfers were obtained using the following query:
SELECT [from], [to], sum(amount) as total_amount, DAY(timestamp) as day, MONTH(timestamp) as month, YEAR(timestamp) as year FROM TxTransfers WITH (NOLOCK) where amount_symbol = 'SBD' and [type] = 'transfer' and ([from] in ('poloniex', 'bittrex', 'blocktrades', 'openledger', 'openledger-dex','hitbtc-exchange', 'hitbtc-payout', 'changelly', 'freewallet.org', 'freewallet', 'coinpayments.net', 'rudex', 'binance-hot', 'deepcrypto8', 'steemexchanger', 'upbit-exchange', 'myupbit', 'upbitsteemhot', 'upbituserwallet', 'gopax', 'gopax-deposit', 'huobi-pro', 'huobi-withdrawal', 'bithumb.hot') or [to] in ('poloniex', 'bittrex', 'blocktrades', 'openledger', 'openledger-dex','hitbtc-exchange', 'hitbtc-payout', 'changelly', 'freewallet.org', 'freewallet', 'coinpayments.net', 'rudex', 'binance-hot', 'deepcrypto8', 'steemexchanger', 'upbit-exchange', 'myupbit', 'upbitsteemhot', 'upbituserwallet', 'gopax', 'gopax-deposit', 'huobi-pro', 'huobi-withdrawal', 'bithumb.hot')) AND YEAR(timestamp) = 2018 GROUP BY [from], [to], DAY(timestamp), MONTH(timestamp), YEAR(timestamp) order by YEAR(timestamp), MONTH(timestamp), DAY(timestamp) ascWith values for amount_symbol changing between "SBD" and "STEEM" for each YEAR(timestamp) change (2018 is displayed in this example).
The output of these queries are stored in the
<year>_<currency>_exchange_transfers.csvfiles such as2018_steem_exchange_transfers.csvThe query looks at all transfers where either the from or to involves a known exchange wallet and sums those amounts per day, per account. For example, if you transfer 100 STEEM to an exchange and then transfer 90 STEEM back from an exchange on the same day, what will be recorded is a 10 STEEM withdrawal.
The
steem_exchange_transfers.phpscript parses each day and keeps a running total of withdrawals and deposits for each account using USD values for STEEM or SBD on that day.
Why:
You might ask why I bothered to do this. For years now, I've been running the weekly exchange transfer report. Here's an example. I view the value of the Steem blockchain as a sort of shared collaborative commons. Though we each have our own stake and property on the blockchain, our actions impact the value of everyone else's stake. Unlike most blockchains, the inflation here is being distributed via a rewards pool which we share a bit of responsibility in protecting using "proof of brain" as described in the Steem white paper.
When value from the rewards pool is distributed to Steem accounts, what they do with that Steem impacts everyone. If they regularly dump it on the market, that creates sell pressure for the STEEM and SBD tokens which could lower the value of everyone else's investment. If, on the other hand, they hold their tokens and buy more, it creates buy pressure which could increase the value of everyone else's investment. Without the actual data, it's not easy to determine who is extracting value and who is adding value. It's also important to keep in mind that transferring value to an exchange does not automatically mean the account sold on that exchange.
Results:
To view the results, check out the following files:
Negative numbers mean the account withdrew more value from their account to an exchange than they deposited from an exchange in a given year. Positive numbers mean the account deposited more value to their account from an exchange they withdrew from their account.
I CAN NOT GUARANTEE THE ACCURACY OF THIS DATA
Please, assume this information is not accurate until you've gone through the code and verified it yourself. If you see a bug or a problem with how this data is collected, please let me know by opening an issue ticket or submitted a pull request to fix it.
Edit: I fixed a bug where the SBD prices weren't being used properly which you can see here.
I also removed the example images from the dataset here to ensure people don't confuse them with the same rankings I've done previously in my exchange transfer reports. There are no rankings in this data. It's just data.
Also, it's worth pointing out that the blocktrades account is a special case because it's not just an exchange like all the others. It also acts as a service account for things like buying Steem Power with SBD and is a personal account for a user here.
If you find any errors in this data, please let me know so I can improve the code.
Here are some relevant posts of mine related to why I find this information interesting and useful:
- An Argument for Long-Term Rational Self-Interest Versus Short-Term Irrational Value Extraction
- Psychological Egoism: Selfishly Improving the World Around You
- The Steemit $$$ Challenge: Prove To Yourself Why You Are Here
- The Currency of Steemit Isn't Steem Power, STEEM, or SBD
- Privacy, Identity, and Human Flourishing
Let me know what you think of this data. Should this level of transparency be used to help clarify real intentions based on actions, not just words or opinions? Should the reputations of people be impacted by the value added or extracted from the blockchain financially? Is the rewards pool a shared collaborative commons and if so, what responsibility (if any) should we take as individuals knowing how our actions impact others?
Hi Luke, can you help me set up a node/mining pool for a small cap coin? I just have a few questions, I'm assuming you have the knowledge?
I'm not currently involved in PoW mining. You can see my history with it here.
Dang! that's a lot of bitcoin. And I've been sulking over getting hacked for 4.5 lol Put's things in perspective.
Here is an idea, this list of sellers should be on the front page of Steemit (and Busy) so everyone can be reminded who the top sellers are and see it as it happens on the Dashboard.
People might want to know why the Steem price dropped from around 90 cents in October to 25 cents a few months later.
The front page? This would make sense to new users, or they would say "WTF is this?" and close the window? I think you're trying to make a point which may have some validity but doing so poorly.
As I discussed with lukestokes privately, I believe this is highly inaccurate and misleading due to various factors including the blocktrades account being an enigma. If the blocktrades account were some small thing on page three that might not matter but its size is enormous, rendering the rest of the list highly distorted and largely meaningless.
Also, people have multiple accounts. There are some of my accounts near the top and some near the bottom. What does this mean? In and of itself basically nothing.
@lukestokes has been pretty good about qualifying his weekly exchange reports in terms of what they mean or don't mean, and I've been supportive of that. It is actually easier to avoid too much distortion in that case because if some transfers occur one week but which aren't characterized correctly or where the meaning is unclear, at least that drops out in the next week. But in this case the aggregation of a lot of misleading and incorrect data is just too much to view as very useful, potentially not even at all.
If the data is "inaccurate" please clarify exactly what data is wrong and how. "Various factors" is not specific enough to be meaningful to me. The blocktrades account is both a personal account and an exchange account. That makes it quite difficult to compare to others. Are you suggesting it not be included? I didn't exclude any accounts. Blocktrades is also built directly into the Steemit website which means it gets used a lot. All the data is there in the csv files. If something is wrong, people point it out specifically. IMO, it doesn't "distort the list" in any way. The other numbers are (unless the code is wrong) an accurate representation of the net amount of transfers to exchanges. It's meaningful to me and others. It may be meaningless to you, and that's fine. I just gave a representative screenshot in my post, but if you click through you can see the entire dataset is there including all accounts which have ever interacted with exchanges.
Yes, many have multiple accounts. That's kind of the point of putting information like this out there. People are now free to use the data files in any way they like to aggregate accounts together if they so choose. Another version of this I had in mind was to do exactly that using some form of crowdsourced account relationships (ngc+bernie+nextgen as an example).
This was not a "ranking" just an ordered list, so there was no ranking "meaning" intended which you may be looking for and not finding.
Which transfers (beyond blocktrades providing non-exchange services and personal transfers on the same account) do think are characterized incorrectly here?
Again, which meanings are unclear? The USD value of the transfers are calculated based on the STEEM and SBD value of the transfer on the day of the transfer. What is unclear?
Please, again, be specific. What data presented here is misleading or incorrect (other than the already mentioned blocktrades account which is the only exchange account in the list that I know of with multiple uses beyond just an exchange)?
The total amount of value people take out (or put in) to the Steem blockchain should matter to us all. If some find it interesting and others don't, that's fine with me. If the data is actually inaccurate because of a bug, I'll fix it.
As I noted elsewhere when you have $40 million in blocktrades+alpha (and there may be other associated accounts) that are mostly opaque and you don't know what they are doing, this throws off the entire dataset. This isn't a $40 slop factor, it is a $40 million slop factor. That is enormous.
I don't know because the blocktrades collections of accounts are opaque. There may well be exchange transactions not shown as such and non-exchange transfers shown as exchange. As you say the blocktrades account is included in the web site. Apart from the sheer magnitude of at least $40 million, the fact that it is used a lot means that getting it wrong can carry over and bias a lot of the data.
There is no such thing as putting in or taking out of the blockchain (other than burn I suppose). All that can be done is to transfer between accounts. Exchange accounts have some properties that relate to buying and selling but sometimes not. You might send to an exchange and leave it there (or vice versa), or send to exchange from one account and from exchange to another. You haven't put in or taken out value in any of these steps, just moved it between accounts.
Of course at some level you just did a SQL query (with certain embedded assumptions, which are transparent) and here is the output. I'm not disputing that part of it at all. Just that when you are trying to accomplish something beyond the literal query itself, the mapping between the SQL query and meaningful conclusions is important (and up to the reader).
I don't know if the there is a bug. As we discussed, the aggregates on the steemit accounts don't pass sanity checks relative to things like headcount and burn rate, which says to me that something is wrong. Maybe it is just a matter of the blocktrades accounts issue, or maybe something else, I don't know.
Another possible explanation would be additional exchange accounts (possibly lesser-known and/or not public) missing from the list, which means transactions to and from those accounts are also missing. Again, I don't know the exact flaw but it is clear from both perspectives (top down and bottom up) that there are major inaccuracies here (not inaccuracies in so far as the output of the SQL query, but in the numbers actually representing an accurate aggregate of what the title says they do.
I disagree. It may throw off comparisons between accounts used as exchanges for buying and selling, but I'm not trying to make conclusions about any such comparisons.
Yes, I understand this, but exchange accounts clearly do have special meaning and are even listed in some accounts like Vessel. I think that meaning is important enough to highlight and people do "deposit" or "withdraw" from exchanges all the time for all blockchains. Just because the blockchain itself didn't burn or create tokens doesn't change the meaning of the interaction as commonly understood.
IMO, if people hold money on exchanges, they don't own the cryptocurrency any more. I see this as RULE #1. If they move to an exchange and then back off an exchange to a different account, the data would accurately show that as one withdrawal and one deposit.
Well, if it's something specific leading to a problem, I'll happily make improvements.
Without knowing the exact flow, it's hard to make your input actionable. The data, as far as I know, does represent the sum of the daily net USD value of transactions to and from known exchange accounts. Any conclusions beyond that are up to others to figure out.
Okay, we disagree then. IMO when there is $40 million of slop it throws off most meaningful interpretations here. Yes we can say that such and such account sent or received $X worth of tokens to 'known' exchanges, but we can't really say how that compares with other accounts or the totals. If said account interacted with the blocktrades-related accounts (and/or other unidentified exchange accounts) in some manner we don't understand, it may be that the $X going to 'known' exchanges is very, very different from the amount going to actual (known and unknown) exchanges.
There is such a thing as error bounds, and the error bounds seem to be at least $40 million in magnitude. That is simply enormous relative to the flows going on here.
i would agree with that. Where we may disagree is how much additional interpretation is necessary to reach any sort of meaningful conclusions is required, or if it is even possible.
It may in fact not be actionable. The Steem blockchain has a degree of transparency but as we have seen from the results, being your best effort and I'm sure a sincere one, not passing basic sanity checks, it is not clear there is sufficient transparency to reach meaningful conclusions. Or perhaps if some additional mysteries can be untangled the gap can be narrowed.
Yes transparency need in this platform.
slowly, but more and more, you make me understand the whole steem thingy... thanks! and just an amateur question: when I buy steempower with my steem (which I did in 2018) I will show up as withdrawing money(which it does)! Right? But I never really withdrew anything...!?
I don't understand what you mean by "buying SteemPower with Steem." Are you referring to a @blocktrades specific service? Unfortunately that account is not a traditional exchange account like all the others. It's a personal account which comments, votes, powers up, powers down, provides services, etc as well as being an exchange account. I don't see there being such a thing as "buying Steem Power" as STEEM is Steem Power once you vest it. If you are renting Steem Power and paying for it with Steem, that is a different interaction. You're paying for a service which is (unfortunately) done via an account which is also an exchange.
as I can remember I had some steemdollars in the savings which I took out one day to convert to steem power and the only way I found to do that was via blocktrades...
just asking because in your txt-files from 2018 my name is listed with some 3.60 something... and I just wanted to know if this is that trade I made..? I never withdrew anything. Just trying to understand the "money" side of the reward system behind all this.
If it wasn't that, may you can enlighten me why my name is listed with this amount?! Thank you anyway for the response and the work you do.
Dan
Sweet! Now it's exactly what we expect:
Thanks for helping me fix this bug. :)
excellent, I'm glad to have helped you... big pleasure!
Ah! I see a bug in the code.
is called instead of using SBD price when it should! I'll update things.
Looks like you spent a total of 4.464 SBD on 2018-2-11:
I think that's where the 3.60 comes from:
But what confuses me is the historical price listed for SBD on 11/2/2018 is
0.9797255so it looks like something is off there as I would expect that to be4.37(4.83 * 4.464) not 3.60.I may have to dig into this a bit more. Thanks!
From this list a "Loyalty Score" could be generated provided that the amount transferred to the exchange is only one factor in a multi-dimensional analysis of the metrics.
"Should the reputations of people be impacted by the value added or extracted from the blockchain financially" i believe the reputation of people should be impacted not extracted. Gosh! I guess been a programmer must really be difficult to understand all these codes
There certainly in my opinion should be a score but I don't think it's reputation specifically. I am also not against people cashing out because people have bills. It is all about rates they are doing it, when they are doing it, how they are doing it, there is more to it than just powering down and for this reason I cannot simply say just because an account powdered down that it means anything.
I'm powering down myself right now even. It's mainly as a reaction from certain others ahead of me powering down. I don't want to be the one who isn't powering down when the biggest players are.
I also think it does matter how much a person powers down in a year. If a person powers down millions of estimated USD value, or just millions of Steem, it's vastly different from powering down several thousand Steem.
How does this relate with people wanting the stake to be more decentralized? Isn't it in some sense better, or at least neutral, that larger accounts power down? If only the opposite happens the stake gets more centralized.
Amazing work.
This post has been included in the latest edition of SOS Daily News - a digest of all you need to know about the State of Steem.
Editor of the The State of Steem SoS Daily News.
Promoter of The State of Steem SoS Weekly Forums.
Editor of the weekly listing of steem radio shows, podcasts & social broadcasts.
Founder of the A Dollar A Day charitable giving project.