There probably are already tools that calculate curation rewards out there. But it is fun to do so programatically. We could get such data directly from the Hive blockchain. Or we could also use HiveSQL, which is easier and faster.
You may want to know your monthly curation rewards to see the returns on your HP investment, or to see how much other HP investors are earning in curation and compare. Such information can help with making better decisions.
For example, some investors can earn rewards on their Hive Power with participating in curation activities or some may choose to buy HBD instead and earn annual 10% interest with low risks on the underlying asset (HBD). I prefer investing in HP. Because it enables more active participation in the Hive network, rewards distribution, and governance. It also has no limits on how high the price of the underlying asset (Hive) can go over longer period of time, while also having a risk of going down in price significantly.
Let's assume price of Hive doesn't change within a year, can Hive Power return similar earning like HBD. Can HP earn 10% a year in curation rewards? Let's find out with writing some code and getting this information using HiveSQL.
I personally prefer to use HiveSQL query commands within a python code which helps automating and reusing the code for other projects. But you can use any other SQL app or tool to connect to the HiveSQL database.
To start I use my simple python script template that has a function to connect to HiveSQL:
import os
import pymssql
def hive_sql(SQLCommand, limit):
db = os.environ['HIVESQL'].split()
conn = pymssql.connect(server=db[0], user=db[1], password=db[2], database=db[3])
cursor = conn.cursor()
cursor.execute(SQLCommand)
result = cursor.fetchmany(limit)
conn.close()
return result
hive_sql
function receives SQLCommand
as the first argument which contains SQL query code. Second parameter limit
is there to set the limit of the results received back from HiveSQL.
I am using os.environ[]
to store the private keys that are provided by HiveSQL to connect to the database. If the code is only used locally and not publicly accessible, there is no need for this. I used to just put in the keys within the script, in the past. Now I use this method, so I can upload files on github and/or use them within heroku apps without worrying that private keys being exposed.
Before we try to get the curation rewards let's connect to HiveSQL and get a global variable that helps with converting vests to hive. We will need it later. Because HP and curation rewards are represented in vests on Hive blockchain, we need this extra step.
SQLCommand = '''
SELECT hive_per_vest
FROM DynamicGlobalProperties
'''
hpv = hive_sql(SQLCommand,1)[0][0]
Now let's see how much HP I have. If we making this sql query within python we can just reuse hpv
variable within the sql code as following:
SQLCommand = f'''
select name,
(vesting_shares + received_vesting_shares - delegated_vesting_shares) * {hpv}
from Accounts
where name = 'geekgirl'
'''
hp = hive_sql(SQLCommand, 1)
print(hp)
Alternatively, we can include a subquery within our sql command to get the hive_per_vest value:
SQLCommand = '''
select name,
(vesting_shares + received_vesting_shares - delegated_vesting_shares) *
(select hive_per_vest from DynamicGlobalProperties)
from Accounts
where name = 'geekgirl'
'''
hp = hive_sql(SQLCommand, 1)
print(hp)
Second option is probably better, since it can be used in other sql query apps and we don't have to make two separate queries.
Accounts
table in HiveSQL has rows/values like vesting_share
(owned hp), received_vesting_shares
(receive hp delegation), delegated_vesting_shares
(hp delegated away). We need all three values to calculate the current hp of the account.
There is one more useful row/value we can use - vesting_withdraw_rate
. It helps us to see the power down rate, which lowers the hp. For simplicity I am not going to use it here.
Now that we know the current hp, let's get the curation rewards for the month of August.
SQLCommand = '''
select SUM(reward) *
(select hive_per_vest from DynamicGlobalProperties)
from VOCurationRewards
where curator = 'geekgirl'
and timestamp between '2021-08-01' and '2021-09-01'
'''
curation_rewards = hive_sql(SQLCommand, 1)
print(curation_rewards)
VOCurationRewards
table has rows/values like reward, curator and timestamp among others. These should be enough for us to get the sum of rewards for the months of August.
Using hp and curation rewards results we can calculate what the annual earnings are. We could also get the curations rewards for the whole year, instead of one month. I prefer to use only one month, because HP changes over time; users can power up or power down, delegate or undelegate. One year is a long time to assume that account had maintained the current amount of HP thought the entire year. Even basing the calculation based on one month many not be 100% accurate. But I think reasonable enough to get approximations.
Alternatively, we can combine the two queries into one and get as result: name, current HP, previous month's curation rewards, and approximate annual earnings.
SQLCommand = '''
select name,
(vesting_shares + received_vesting_shares - delegated_vesting_shares) *
(select hive_per_vest from DynamicGlobalProperties),
(select SUM(reward)
from VOCurationRewards
where curator = name
and timestamp between '2021-08-01' and '2021-09-01') *
(select hive_per_vest from DynamicGlobalProperties),
((select SUM(reward)
from VOCurationRewards
where curator = name
and timestamp between '2021-08-01' and '2021-09-01') *
(select hive_per_vest from DynamicGlobalProperties)) * 12 /
((vesting_shares + received_vesting_shares - delegated_vesting_shares) *
(select hive_per_vest from DynamicGlobalProperties)) * 100
from Accounts
where name = 'geekgirl'
'''
result = hive_sql(SQLCommand, 1)
print(result)
This code is now getting a little bit messy and using too many subqueries. If you know a better way, feel free to let me know in the comments. But it is using the same logic as we have been following so far.
The results show: I have 51,459 HP, curation rewards for August were 454 HP, and this resulted in approximately 10.5% annual earnings.
Now that I can calculate curation rewards for one account, I should be able to do the same for multiple accounts and compare the results.
SQLCommand = '''
select name,
(vesting_shares + received_vesting_shares - delegated_vesting_shares) *
(select hive_per_vest from DynamicGlobalProperties),
(select SUM(reward)
from VOCurationRewards
where curator = name
and timestamp between '2021-08-01' and '2021-09-01') *
(select hive_per_vest from DynamicGlobalProperties),
((select SUM(reward)
from VOCurationRewards
where curator = name
and timestamp between '2021-08-01' and '2021-09-01') *
(select hive_per_vest from DynamicGlobalProperties)) * 12 /
((vesting_shares + received_vesting_shares - delegated_vesting_shares) *
(select hive_per_vest from DynamicGlobalProperties)) * 100
from Accounts
where (vesting_shares + received_vesting_shares - delegated_vesting_shares) *
(select hive_per_vest from DynamicGlobalProperties) > 50000
order by (vesting_shares + received_vesting_shares - delegated_vesting_shares) desc
'''
result = hive_sql(SQLCommand, 1000)
table = buildTable(result)
print(table)
In the code above we are making a query to get name, hp, curation rewards for August, and annual percentage estimation for all accounts that have more than 50k HP.
You may have noticed buildTable
function. It is a helper function that formats the results so that I can display the results in a table format.
def buildTable(data):
table = '# Name HP Curation Annual % '
count = 1
for row in data:
if row[2]:
row_html = f'{count} {row[0]} {round(row[1],0):,} {round(row[2],0):,} {round(row[3],2)} '
count += 1
table += row_html
else:
row_html = f'{count} {row[0]} {round(row[1],0):,} 0 0 '
count += 1
table += row_html
table += ''
return table
When we run the script we get the following results. You will see how very few accounts can earn more than 15% in curation, some less than 10%, and some don't get curation rewards at all.
Curation Rewards For August (50K+ HP)
# Name HP Curation Annual % 1 appreciator 8,462,092 74,561 10.57 2 ocdb 7,365,955 65,967 10.75 3 blocktrades 5,810,086 47,586 9.83 4 smooth 3,632,246 25,115 8.30 5 rocky1 2,755,571 24,633 10.73 6 mottler 2,475,650 0 0 7 buildawhale 2,187,832 19,903 10.92 8 blocktrades.com 2,144,236 1,840 1.03 9 theycallmedan 2,058,461 18,193 10.61 10 curangel 1,912,744 18,215 11.43 11 alpha 1,770,798 14,662 9.94 12 newsflash 1,729,031 10,692 7.42 13 leo.voter 1,691,424 7,320 5.19 14 bdvoter 1,642,497 14,412 10.53 15 threespeak 1,604,868 12,192 9.12 16 xeldal 1,459,629 8,862 7.29 17 ecency 1,452,947 9,818 8.11 18 ranchorelaxo 1,275,639 8,751 8.23 19 trafalgar 1,205,006 10,526 10.48 20 darthknight 1,180,361 8,942 9.09 21 steemmonsters 1,134,408 3,158 3.34 22 singhcapital 1,081,572 2,150 2.38 23 tipu 1,073,485 8,907 9.96 24 themarkymark 1,017,670 9,074 10.70 25 altleft 1,014,574 6,790 8.03 26 adm 933,738 213 0.27 27 sweetsssj 906,346 7,461 9.88 28 tribesteemup 900,529 7,653 10.20 29 safari 804,311 0 0 30 smooth-e 802,588 0 0 31 gtg 721,575 6,017 10.01 32 abit 704,161 6,543 11.15 33 enki 684,874 2,951 5.17 34 steempty 650,627 5,272 9.72 35 steemed-proxy 588,122 5,393 11.00 36 usainvote 583,728 4,958 10.19 37 ocd 554,949 4,217 9.12 38 jphamer1 500,762 4,471 10.71 39 recursive 495,657 45 0.11 40 azircon 482,526 4,359 10.84 41 balte 482,111 3,889 9.68 42 minnowsupport 460,426 2,487 6.48 43 bitcube 459,596 0 0 44 neoxian 456,866 3,678 9.66 45 canadian-coconut 441,855 3,862 10.49 46 curie 438,559 4,018 10.99 47 v4vapid 436,773 1,875 5.15 48 arhag 401,135 0 0 49 broncnutz 390,684 2,985 9.17 50 dbuzz 365,994 2,845 9.33 51 ctime 361,809 3,471 11.51 52 ramta 359,484 0 0 53 haejin 347,636 2,767 9.55 54 upmewhale 343,570 3,143 10.98 55 qurator 339,374 3,369 11.91 56 acidyo 335,525 3,026 10.82 57 vcelier 331,069 2,236 8.10 58 newhope 312,713 2,803 10.75 59 coinomite 311,832 2,598 10.00 60 riverhead 301,769 0 0 61 arcange 291,894 2,565 10.54 62 dtube 288,405 1,218 5.07 63 znnuksfe 276,703 2,045 8.87 64 ua-promoter 274,942 2,618 11.43 65 alexis555 272,307 2,431 10.71 66 trostparadox 262,024 2,032 9.31 67 creator 255,647 0 0 68 roelandp 254,592 1,581 7.45 69 mmmmkkkk311 251,823 2,102 10.02 70 onealfa 235,457 2,016 10.27 71 actifit 234,615 1,914 9.79 72 healthsquared 232,291 2,178 11.25 73 pfunk 230,800 2,226 11.58 74 dhenz 224,432 0 0 75 edicted 221,734 1,785 9.66 76 brofi 218,185 1,174 6.46 77 cervantes 217,416 1,771 9.78 78 stoodkev 216,886 1,774 9.81 79 nautilus-up 215,665 2,094 11.65 80 life-timer 215,546 2,051 11.42 81 fatimajunio 210,892 1,836 10.45 82 project.hope 209,948 1,739 9.94 83 ssg-community 209,362 1,653 9.48 84 tarazkp 203,888 2,101 12.36 85 nbs.gmbh 201,894 1,502 8.93 86 waivio.match 199,674 1,284 7.72 87 redes 198,452 1,773 10.72 88 arpolkin 197,387 1,505 9.15 89 gringalicious 194,057 1,845 11.41 90 vancouverdining 193,554 45 0.28 91 ripperone 192,146 1,713 10.70 92 hive.curation 189,657 1,713 10.84 93 therealyme 189,542 1,714 10.85 94 sean-king 186,187 0 0 95 rima11 180,249 1,603 10.67 96 done 178,987 1,595 10.69 97 pdq 177,930 1,474 9.94 98 stayoutoftherz 176,121 1,602 10.91 99 ezrider 173,964 1,534 10.58 100 zuerich 172,303 1,586 11.04 101 slobberchops 169,882 1,568 11.07 102 minnowbooster 169,477 1,317 9.33 103 cervantes.one 168,353 1,364 9.72 104 steembasicincome 168,278 1,554 11.08 105 pharesim 165,052 266 1.93 106 hivegc 164,771 1,511 11.01 107 ph-support 164,425 2,038 14.88 108 dhimmel 163,485 1,159 8.51 109 spt-skillup 160,766 2,253 16.82 110 fuerza-hispana 158,968 1,429 10.78 111 spectrumecons 158,939 1,364 10.30 112 extrospect 157,951 502 3.82 113 vortac 157,786 1,075 8.17 114 steemstem 157,444 208 1.58 115 lazy-panda 157,134 0 0 116 gunthertopp 156,166 1,352 10.39 117 smartsteem 155,338 1,436 11.09 118 birdwatcher 150,440 1,393 11.11 119 peakd 150,051 632 5.05 120 node1 149,235 0 0 121 geneeverett 148,767 1,212 9.77 122 foxon 147,834 1,319 10.70 123 pishio 147,525 966 7.85 124 hanshotfirst 145,694 296 2.44 125 diggndeeper.com 145,428 1,264 10.43 126 hbdstabilizer 143,536 0 0 127 mangos 137,919 1,246 10.84 128 holiday 136,602 0 0 129 brettpullen 134,316 689 6.15 130 wackou 130,994 2 0.02 131 oflyhigh 130,485 1,294 11.90 132 s4s 130,404 1,047 9.64 133 daveks 128,126 1,170 10.96 134 zioland 127,523 0 0 135 laruche 126,675 1,139 10.79 136 investegg 126,632 973 9.22 137 bitcointalker 125,471 0 0 138 moderator 124,698 0 0 139 coininstant 121,825 1,024 10.09 140 postpromoter 120,210 1,047 10.45 141 pouchon 120,078 879 8.79 142 gleam-of-light 118,904 1,023 10.33 143 natsu 117,992 0 0 144 ace108 117,766 833 8.49 145 sepracore 116,985 972 9.97 146 wagginston 116,527 42 0.43 147 aggroed 113,353 10 0.10 148 kommienezuspadt 113,036 703 7.46 149 nateaguila 112,981 1,190 12.64 150 samantha-w 112,915 0 0 151 abh12345 112,267 1,252 13.38 152 penguinpablo 111,805 1,218 13.07 153 msp-curation 110,857 427 4.62 154 trumpman 110,135 942 10.27 155 msp-waves 109,335 229 2.52 156 node2 108,442 0 0 157 eturnerx 108,261 873 9.68 158 hiro-hive 107,786 984 10.95 159 gopaxkr 107,705 0 0 160 cornucopia 106,706 0 0 161 therising 106,591 979 11.02 162 smasssh 105,835 944 10.71 163 isaria 101,682 877 10.35 164 deanliu 101,398 931 11.02 165 traf 101,352 883 10.45 166 biggest 101,342 0 0 167 sbi2 100,729 878 10.45 168 dcityrewards 100,245 983 11.77 169 c0ff33a 99,638 888 10.69 170 bcc 99,628 797 9.60 171 solominer 99,619 883 10.64 172 btu 99,090 825 9.99 173 yabapmatt 99,029 309 3.74 174 howo 98,110 848 10.37 175 galenkp 97,830 975 11.96 176 vincentcestone 97,448 0 0 177 sandymeyer 96,808 815 10.11 178 b0y2k 96,257 104 1.30 179 mbp 95,856 770 9.64 180 magicmonk 94,768 844 10.69 181 shaka 94,433 791 10.06 182 eddiespino 94,432 0 0.00 183 jim888 93,383 811 10.42 184 hive-117778 92,774 789 10.21 185 goblinknackers 92,451 813 10.56 186 ozchartart 91,567 0 0 187 sbi3 90,323 828 11.00 188 fedesox 90,092 3,463 46.12 189 discovery-it 88,699 377 5.10 190 eonwarped 88,091 799 10.89 191 steemcleaners 88,066 801 10.92 192 unpopular 87,746 790 10.81 193 blewitt 86,711 240 3.32 194 someguy123 85,604 586 8.21 195 pinmapple 84,473 620 8.80 196 quochuy 84,393 532 7.57 197 gengua 82,699 633 9.18 198 engrave 81,659 741 10.88 199 condeas 81,532 678 9.98 200 holger80 80,546 73 1.08 201 schlees 80,022 671 10.07 202 gardenofeden 79,839 0 0 203 promoted 79,717 0 0 204 dynamicsteemians 78,472 174 2.66 205 creativeblue 78,356 681 10.43 206 honey-swap 77,412 0 0 207 geoffrey 77,084 0 0 208 spanishgirl3 77,081 0 0 209 msp-mods 76,945 143 2.23 210 buggedout 76,472 619 9.72 211 ocd-witness 75,889 256 4.04 212 rivalhw 75,655 588 9.32 213 cornerstone 75,257 498 7.94 214 kevinwong 75,093 944 15.09 215 dragonslayer109 75,024 628 10.05 216 meesterboom 74,472 674 10.86 217 hive.pizza 74,172 355 5.74 218 votame 74,065 190 3.08 219 intrepidphotos 72,426 624 10.35 220 mustard 71,944 25 0.41 221 netaterra 71,824 651 10.87 222 aliento 71,517 1,080 18.13 223 anyx 70,872 0 0 224 taskmaster4450 70,484 565 9.62 225 jesta 70,238 0 0 226 solarwarrior 70,105 649 11.11 227 rawutah 69,812 479 8.24 228 nonameslefttouse 69,545 508 8.77 229 luigi 69,170 0 0 230 node3 69,168 0 0 231 exhaust 69,093 569 9.89 232 daltono 68,795 658 11.48 233 google 67,561 0 0 234 gregory-f 67,155 816 14.58 235 dsc-r2cornell 67,117 545 9.74 236 larryphang 67,027 3 0.05 237 inertia 66,977 63 1.13 238 publicworker 66,633 0 0 239 smartvote 66,440 366 6.60 240 talentclub 66,095 1,173 21.30 241 sanjeevm 66,023 677 12.31 242 algeorge 65,012 0 0 243 louis88 64,522 504 9.38 244 jongolson 64,364 643 11.98 245 johannpiber 63,307 551 10.44 246 tobetada 62,780 583 11.14 247 enable 61,935 0 0 248 cibersk8 61,529 523 10.21 249 delishtreats 61,179 554 10.88 250 staticinstance 61,129 0 0 251 hodlcommunity 60,285 501 9.98 252 asgarth 60,017 503 10.06 253 noaommerrr 59,933 0 0 254 captainhive 59,482 515 10.39 255 jl777 58,934 0 0 256 jedigeiss 58,658 449 9.19 257 bagholders 58,315 454 9.33 258 tombstone 58,222 352 7.25 259 socky 57,335 0 0 260 ecotrain 56,837 335 7.08 261 ben1 56,812 0 0 262 steevc 56,363 499 10.63 263 lunaticpandora 56,342 528 11.25 264 lemouth 56,094 96 2.05 265 administrator 56,067 0 0 266 emrebeyler 56,043 511 10.94 267 spaminator 55,412 69 1.49 268 stephenkendal 55,342 0 0 269 pgarcgo 55,295 447 9.71 270 janusface 55,189 280 6.09 271 livinguktaiwan 55,053 480 10.46 272 quinneaker 54,958 0 0 273 joshman 54,863 252 5.50 274 infovore 54,625 2 0.03 275 steemychicken1 54,401 465 10.27 276 diahla31 54,327 0 0 277 dan-bn 54,120 0 0 278 senseicat 53,986 0 0 279 thealliance 53,971 505 11.23 280 visionaer3003 53,530 655 14.68 281 sbi4 53,512 508 11.40 282 ctpsb 52,854 478 10.86 283 kryptodenno 52,632 312 7.10 284 midlet 52,356 295 6.75 285 ewkaw 51,675 458 10.64 286 therealwolf 51,582 364 8.47 287 cryptoandcoffee 51,479 742 17.29 288 geekgirl 51,461 454 10.58 289 risingstargame 51,457 460 10.72 290 forykw 51,201 477 11.18 291 dlux-io 50,956 64 1.51 292 thecryptodrive 50,679 70 1.65 293 gooddream 50,592 76 1.79 294 woo7739 50,081 0 0 295 cannonwar 50,051 0 0