Building delegation time series

in cryptocurrency •  7 months ago

In this post we discuss the data selection and the processing used to calculate the end of month delegation balances since delegations started in March 2017.

Let's see the first month of delegation transactions. This is how the delegation data comes from the SteemSQL TxDelegateVestingShares table (

liberosistdunia94,599,1672017-03-30 15:39:21.0
arhagah11,000,0002017-03-30 16:40:54.0
arhagah102017-03-30 16:46:24.0
obamaclinton21,6422017-03-30 18:05:33.0

We see that arhag delegated 1 million VESTS to ah1 on March 30, 2107, at 16:40:54, and that a few minutes later arhag canceled all his delegation to ah1. So arhag had no delegation at the end of March 2017. But obama had. He delegated 21,642 VESTS to clinton on March 30, 2017 and did not changed the amount delegated on that month. The same happened to liberosist and dunia so we can easily calculate the March 2017 delegated balance as 94,620,809 VESTS.

Each time two accounts make a delegation transaction it gets registered in the Steem blockchain as the total one account is debtor/creditor of the other one. As a result of this structure, the last transaction date (timestamp) for each pair of delegator and delegatee in each month is associated with the delegator's balance in that month, if there was a transaction between them in the month. If there wasn’t a transaction between them, we have to look for the last transaction between them back in the blockchain history. Let’s try to write an algorithm in R to extract the end of month balances from the history of delegations.

First we select, for each combination of delegator, delegatee, year and month of delegation, the last timestamp:

dvs <- TxDelegateVestingShares[,list( timestamp = max(timestamp)),by=list(delegator,delegatee,year(ts),month(ts))]

Now we get back to the TxDelegateVestingShares table to pick the ‘vesting_shares’ balance of the timestamps selected previously:

s <- merge(TxDelegateVestingShares,dvs) 

We need to build monthly sequence of dates and initialize the first year, y, and the first month, m:

dts <- unique( s[,list(year,month)] )[order(year,month)]
y <- dts[1,year]
m <- dts[1,month]

Filter delegations by these initial month and year and get the delegation balances at the end of the first month of delegations:

m0 <- s[year == y & month == m,]
bal <- m0[,list(delegator,delegatee,vesting_shares,year,month,ts)]

Follow the annotations embeded in the code below to check what happens in the middle of the loop of dates:

for( i in 2:(nrow(dts)-1) ){

  # Forward one month
  y <- ifelse(m==12, y+1, y)
  m <- ifelse(m==12, 1, m + 1)

  # Filter delegations by the dates of the following month
  m1 <- s[year == y & month == m,]

  # Accounts that had a balance in the first and in the second periods: select only the second period's balances
  n1 <- merge(m0,m1)[,list(delegator,delegatee,vesting_shares=vesting_shares.y,year=year.y,month=month.y,ts=ts.y)]

  # Accounts that had a balance before but whose balance did not change in the current month: select the first period balances
n2 <- m0[!m1][,list(delegator,delegatee,vesting_shares,year=y,month=m,ts)]

  # Accounts that had no balance before but showed a positive balance in the current month: select the second period balances
  n3 <- m1[!m0][,list(delegator,delegatee,vesting_shares,year,month,ts)]

  # From the union of these three sets we get the delegation balances of all Steem accounts in the second month
  res <- rbind( n1, n2, n3 )

   # Stack the result to the monthly balances data.table
  bal <- rbind(bal, res)

  # res is the new m0
  m0 <- res

All the monthly delegation balances were stacked in the "bal" data-table. We only need to sum them by year and month to get to the end of month delegation balances time series:

g <- bal[,list(mvs = sum(vesting_shares)/1000000),keyby=list(year,month)]

From the balances we calculate the rate of growth of delegations:

rg <- cbind( g[1:(nrow(g)-1),list(lag=mvs)], g[2:nrow(g),list(mvs)] )
rg[,rg := 100*((mvs/lag)-1)]

Congratulations to those that like calculations!

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:  

Flagged because I can't recreate your results. I'm calling BS.

You got a 15.94% upvote from @upmewhale courtesy of @verodato!

Earn 100% earning payout by delegating SP to @upmewhale. Visit for details!

To listen to the audio version of this article click on the play image.

Brought to you by @tts. If you find it useful please consider upvoting this reply.

You got a 37.20% upvote from @brupvoter courtesy of @verodato!

You got a 7.69% upvote from @upme thanks to @verodato! Send at least 3 SBD or 3 STEEM to get upvote for next round. Delegate STEEM POWER and start earning 100% daily payouts ( no commission ).

You got a 16.28% upvote from @emperorofnaps courtesy of @verodato!

Want to promote your posts too? Send 0.05+ SBD or STEEM to @emperorofnaps to receive a share of a full upvote every 2.4 hours...Then go relax and take a nap!

You got a 21.15% upvote from @dailyupvotes courtesy of @verodato!

Great post!
Thanks for tasting the eden!

You got a 17.89% upvote from @oceanwhale With 35+ Bonus Upvotes courtesy of @verodato! Delegate us Steem Power & get 100%daily rewards Payout! 20 SP, 50, 75, 100, 150, 200, 300, 500,1000 or Fill in any amount of SP Earn 1.25 SBD Per 1000 SP | Discord server

You got a 19.10% upvote from @minnowvotes courtesy of @verodato!

This post has received a 8.21 % upvote from @booster thanks to: @verodato.

This post has received a 7.92 % upvote from @boomerang.

hello sir really thanks for using our bot min is 0.5 but i know you and you filled some our delegation request on minnowbooster so i upvoted you as free is our gift for you sir , if you like you can be part of delegation system we will send 100% sbds which your delegated sp made if you delegate, ,wish the best for you sir

You got a 12.62% upvote from @postpromoter courtesy of @verodato!

Want to promote your posts too? Check out the Steem Bot Tracker website for more info. If you would like to support the development of @postpromoter and the bot tracker please vote for @yabapmatt for witness!

How does this helps in taking investment decision


Knowing the numbers helps find (and justify) where to put the money

Thank you so much for using our service! You were protected from up to 20% losses!

Help us grow by delegating to us! 100sp, 500SP, 1000SP, ANY SP

You just received 26.27% upvote from @onlyprofitbot courtesy of @verodato!

Congratulations @verodato! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

Award for the total payout received

Click on the badge to view your Board of Honor.
If you no longer want to receive notifications, reply to this comment with the word STOP

To support your work, I also upvoted your post!

Do not miss the last post from @steemitboard!

Participate in the SteemitBoard World Cup Contest!
Collect World Cup badges and win free SBD
Support the Gold Sponsors of the contest: @good-karma and @lukestokes

Do you like SteemitBoard's project? Then Vote for its witness and get one more award!

i think it helps i will make my own research and collect it with this info and i see the final result