def get_validator_daily_stats( self, validator_index: int, from_ts: Optional[Timestamp] = None, to_ts: Optional[Timestamp] = None, ) -> List[ValidatorDailyStats]: """Gets all DB entries for daily staking stats of a validator""" cursor = self.db.conn.cursor() querystr = ('SELECT validator_index,' ' timestamp,' ' start_usd_price,' ' end_usd_price,' ' pnl,' ' start_amount,' ' end_amount,' ' missed_attestations,' ' orphaned_attestations,' ' proposed_blocks,' ' missed_blocks,' ' orphaned_blocks,' ' included_attester_slashings,' ' proposer_attester_slashings,' ' deposits_number,' ' amount_deposited ' ' FROM eth2_daily_staking_details ' ' WHERE validator_index = ? ') querystr, bindings = form_query_to_filter_timestamps( query=querystr, timestamp_attribute='timestamp', from_ts=from_ts, to_ts=to_ts, ) results = cursor.execute(querystr, (validator_index, *bindings)) return [ValidatorDailyStats.deserialize_from_db(x) for x in results]
def get_validator_daily_stats( self, filter_query: Eth2DailyStatsFilterQuery, ) -> List[ValidatorDailyStats]: """Gets all DB entries for validator daily stats according to the given filter""" cursor = self.db.conn.cursor() query, bindings = filter_query.prepare() query = 'SELECT * from eth2_daily_staking_details ' + query results = cursor.execute(query, bindings) daily_stats = [ValidatorDailyStats.deserialize_from_db(x) for x in results] # Take into account the proportion of the validator owned validators_ownership = { validator.index: validator.ownership_proportion for validator in self.get_validators() } for daily_stat in daily_stats: owned_proportion = validators_ownership.get(daily_stat.validator_index, ONE) if owned_proportion != ONE: daily_stat.pnl = daily_stat.pnl * owned_proportion return daily_stats
def test_validator_daily_stats_with_db_interaction( # pylint: disable=unused-argument # noqa: E501 price_historian, database, function_scope_messages_aggregator, ): stats_call_patch = patch( 'requests.get', wraps=requests.get, ) validator_index = 33710 with stats_call_patch as stats_call: stats = get_validator_daily_stats( db=database, validator_index=validator_index, msg_aggregator=function_scope_messages_aggregator, from_timestamp=1613606300, to_timestamp=1614038500, ) assert stats_call.call_count == 1 assert len(stats) >= 6 expected_stats = [ ValidatorDailyStats( validator_index=validator_index, timestamp=1613606400, # 2021/02/18 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0078'), start_amount=FVal('32.66'), end_amount=FVal('32.67'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613692800, # 2021/02/19 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0068'), start_amount=FVal('32.67'), end_amount=FVal('32.68'), missed_attestations=19, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613779200, # 2021/02/20 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0080'), start_amount=FVal('32.68'), end_amount=FVal('32.68'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613865600, # 2021/02/21 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0111'), start_amount=FVal('32.68'), end_amount=FVal('32.69'), missed_attestations=3, proposed_blocks=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613952000, # 2021/02/22 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0078'), start_amount=FVal('32.69'), end_amount=FVal('32.7'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1614038400, # 2021/02/23 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0077'), start_amount=FVal('32.7'), end_amount=FVal('32.71'), missed_attestations=1, ) ] assert stats[:len(expected_stats)] == expected_stats # Make sure that calling it again does not make an external call stats = get_validator_daily_stats( db=database, validator_index=33710, msg_aggregator=function_scope_messages_aggregator, from_timestamp=1613606300, to_timestamp=1614038500, ) assert stats_call.call_count == 1 assert stats[:len(expected_stats)] == expected_stats
def test_validator_daily_stats_with_last_known_timestamp( # pylint: disable=unused-argument # noqa: E501 price_historian, function_scope_messages_aggregator, ): validator_index = 33710 stats = _scrape_validator_daily_stats( validator_index=validator_index, last_known_timestamp=1613520000, msg_aggregator=function_scope_messages_aggregator, ) assert len(stats) >= 6 expected_stats = [ ValidatorDailyStats( validator_index=validator_index, timestamp=1613606400, # 2021/02/18 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0078'), start_amount=FVal('32.66'), end_amount=FVal('32.67'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613692800, # 2021/02/19 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0068'), start_amount=FVal('32.67'), end_amount=FVal('32.68'), missed_attestations=19, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613779200, # 2021/02/20 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0080'), start_amount=FVal('32.68'), end_amount=FVal('32.68'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613865600, # 2021/02/21 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0111'), start_amount=FVal('32.68'), end_amount=FVal('32.69'), missed_attestations=3, proposed_blocks=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613952000, # 2021/02/22 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0078'), start_amount=FVal('32.69'), end_amount=FVal('32.7'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1614038400, # 2021/02/23 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0077'), start_amount=FVal('32.7'), end_amount=FVal('32.71'), missed_attestations=1, ) ] stats.reverse() assert stats[:len(expected_stats)] == expected_stats
def test_validator_daily_stats(price_historian, function_scope_messages_aggregator): # pylint: disable=unused-argument # noqa: E501 validator_index = 33710 stats = _scrape_validator_daily_stats( validator_index=validator_index, last_known_timestamp=0, msg_aggregator=function_scope_messages_aggregator, ) assert len(stats) >= 81 expected_stats = [ ValidatorDailyStats( validator_index=validator_index, timestamp=1607126400, # 2020/12/05 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=ZERO, end_amount=FVal(32), deposits_number=1, amount_deposited=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607212800, # 2020/12/06 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607299200, # 2020/12/07 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607385600, # 2020/12/08 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607472000, # 2020/12/09 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607558400, # 2020/12/10 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607644800, # 2020/12/11 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607731200, # 2020/12/12 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607817600, # 2020/12/13 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607904000, # 2020/12/14 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1607990400, # 2020/12/15 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0120'), start_amount=FVal(32), end_amount=FVal('32.01'), proposed_blocks=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608076800, # 2020/12/16 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0132'), start_amount=FVal('32.01'), end_amount=FVal('32.03'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608163200, # 2020/12/17 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('-0.0001'), start_amount=FVal('32.03'), end_amount=FVal('32.03'), missed_attestations=126, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608249600, # 2020/12/18 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0129'), start_amount=FVal('32.03'), end_amount=FVal('32.04'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608336000, # 2020/12/19 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0127'), start_amount=FVal('32.04'), end_amount=FVal('32.05'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608422400, # 2020/12/20 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0144'), start_amount=FVal('32.05'), end_amount=FVal('32.07'), missed_attestations=1, proposed_blocks=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608508800, # 2020/12/21 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0124'), start_amount=FVal('32.07'), end_amount=FVal('32.08'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608595200, # 2020/12/22 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0121'), start_amount=FVal('32.08'), end_amount=FVal('32.09'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608681600, # 2020/12/23 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0120'), start_amount=FVal('32.09'), end_amount=FVal('32.10'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608768000, # 2020/12/24 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0119'), start_amount=FVal('32.1'), end_amount=FVal('32.11'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1608854400, # 2020/12/25 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.0117'), start_amount=FVal('32.11'), end_amount=FVal('32.13'), ) ] stats.reverse() assert stats[:len(expected_stats)] == expected_stats
def _scrape_validator_daily_stats( validator_index: int, last_known_timestamp: Timestamp, msg_aggregator: MessagesAggregator, ) -> List[ValidatorDailyStats]: """Scrapes the website of beaconcha.in and parses the data directly out of the data table. The parser is very simple. And can break if they change stuff in the way it's displayed in https://beaconcha.in/validator/33710/stats. If that happpens we need to adjust here. If we also somehow programatically get the data in a CSV that would be swell. May raise: - RemoteError if we can't query beaconcha.in or if the data is not in the expected format """ url = f'https://beaconcha.in/validator/{validator_index}/stats' log.debug(f'Querying beaconchain stats: {url}') try: response = requests.get(url) except requests.exceptions.RequestException as e: raise RemoteError( f'Beaconcha.in api request {url} failed due to {str(e)}') from e if response.status_code != 200: raise RemoteError( f'Beaconcha.in api request {url} failed with code: {response.status_code}' f' and response: {response.text}', ) soup = BeautifulSoup(response.text, 'html.parser', parse_only=SoupStrainer('tbod')) if soup is None: raise RemoteError( 'Could not find <tbod> while parsing beaconcha.in stats page') try: tr = soup.tbod.tr except AttributeError as e: raise RemoteError( 'Could not find first <tr> while parsing beaconcha.in stats page' ) from e timestamp = Timestamp(0) pnl = ZERO start_amount = ZERO end_amount = ZERO missed_attestations = 0 orphaned_attestations = 0 proposed_blocks = 0 missed_blocks = 0 orphaned_blocks = 0 included_attester_slashings = 0 proposer_attester_slashings = 0 deposits_number = 0 amount_deposited = ZERO column_pos = 1 stats: List[ValidatorDailyStats] = [] while tr is not None: for column in tr.children: if column.name != 'td': continue if column_pos == 1: # date date = column.string try: timestamp = create_timestamp(date, formatstr='%d %b %Y') except ValueError as e: raise RemoteError( f'Failed to parse {date} to timestamp') from e if timestamp <= last_known_timestamp: return stats # we are done column_pos += 1 elif column_pos == 2: pnl = _parse_fval(column.string, 'income') column_pos += 1 elif column_pos == 3: start_amount = _parse_fval(column.string, 'start amount') column_pos += 1 elif column_pos == 4: end_amount = _parse_fval(column.string, 'end amount') column_pos += 1 elif column_pos == 5: missed_attestations = _parse_int(column.string, 'missed attestations') column_pos += 1 elif column_pos == 6: orphaned_attestations = _parse_int(column.string, 'orphaned attestations') column_pos += 1 elif column_pos == 7: proposed_blocks = _parse_int(column.string, 'proposed blocks') column_pos += 1 elif column_pos == 8: missed_blocks = _parse_int(column.string, 'missed blocks') column_pos += 1 elif column_pos == 9: orphaned_blocks = _parse_int(column.string, 'orphaned blocks') column_pos += 1 elif column_pos == 10: included_attester_slashings = _parse_int( column.string, 'included attester slashings') # noqa: E501 column_pos += 1 elif column_pos == 11: proposer_attester_slashings = _parse_int( column.string, 'proposer attester slashings') # noqa: E501 column_pos += 1 elif column_pos == 12: deposits_number = _parse_int(column.string, 'deposits number') column_pos += 1 elif column_pos == 13: amount_deposited = _parse_fval(column.string, 'amount deposited') column_pos += 1 column_pos = 1 prices = [ query_usd_price_zero_if_error( A_ETH, time=time, location='eth2 staking daily stats', msg_aggregator=msg_aggregator, ) for time in (timestamp, Timestamp(timestamp + DAY_IN_SECONDS)) ] stats.append( ValidatorDailyStats( validator_index=validator_index, timestamp=timestamp, start_usd_price=prices[0], end_usd_price=prices[1], pnl=pnl, start_amount=start_amount, end_amount=end_amount, missed_attestations=missed_attestations, orphaned_attestations=orphaned_attestations, proposed_blocks=proposed_blocks, missed_blocks=missed_blocks, orphaned_blocks=orphaned_blocks, included_attester_slashings=included_attester_slashings, proposer_attester_slashings=proposer_attester_slashings, deposits_number=deposits_number, amount_deposited=amount_deposited, )) tr = tr.find_next_sibling() return stats
def test_validator_daily_stats_with_db_interaction( # pylint: disable=unused-argument # noqa: E501 price_historian, database, function_scope_messages_aggregator, eth2, ): stats_call_patch = patch( 'requests.get', wraps=requests.get, ) validator_index = 33710 public_key = '0x9882b4c33c0d5394205b12d62952c50fe03c6c9fe08faa36425f70afb7caac0689dcd981af35d0d03defb8286d50911d' # noqa: E501 dbeth2 = DBEth2(database) dbeth2.add_validators([ Eth2Validator( index=validator_index, public_key=public_key, ownership_proportion=ONE, ), ]) with stats_call_patch as stats_call: filter_query = Eth2DailyStatsFilterQuery.make( validators=[validator_index], from_ts=1613606300, to_ts=1614038500, ) stats, filter_total_found, sum_pnl, sum_usd_value = eth2.get_validator_daily_stats( filter_query=filter_query, only_cache=False, msg_aggregator=function_scope_messages_aggregator, ) assert stats_call.call_count == 1 assert len(stats) >= 6 assert filter_total_found >= 6 expected_stats = [ ValidatorDailyStats( validator_index=validator_index, timestamp=1613606400, # 2021/02/18 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.00784'), start_amount=FVal('32.66'), end_amount=FVal('32.67'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613692800, # 2021/02/19 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.00683'), start_amount=FVal('32.67'), end_amount=FVal('32.68'), missed_attestations=19, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613779200, # 2021/02/20 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.00798'), start_amount=FVal('32.68'), end_amount=FVal('32.68'), ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613865600, # 2021/02/21 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.01114'), start_amount=FVal('32.68'), end_amount=FVal('32.69'), missed_attestations=3, proposed_blocks=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1613952000, # 2021/02/22 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.00782'), start_amount=FVal('32.69'), end_amount=FVal('32.7'), missed_attestations=1, ), ValidatorDailyStats( validator_index=validator_index, timestamp=1614038400, # 2021/02/23 start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=FVal('0.00772'), start_amount=FVal('32.7'), end_amount=FVal('32.71'), missed_attestations=1, ) ] assert stats[:len(expected_stats)] == expected_stats assert sum_pnl >= sum(x.pnl for x in expected_stats) assert sum_usd_value >= sum(x.pnl * ((x.start_usd_price + x.end_usd_price) / 2) for x in expected_stats) # noqa: E501 # Make sure that calling it again does not make an external call stats, filter_total_found, _, _ = eth2.get_validator_daily_stats( filter_query=filter_query, only_cache=False, msg_aggregator=function_scope_messages_aggregator, ) assert stats_call.call_count == 1 assert stats[:len(expected_stats)] == expected_stats # Check that changing ownership proportion works dbeth2.edit_validator( validator_index=validator_index, ownership_proportion=FVal(0.45), ) stats, filter_total_found, _, _ = eth2.get_validator_daily_stats( filter_query=filter_query, only_cache=False, msg_aggregator=function_scope_messages_aggregator, ) last_stat = stats[:len(expected_stats)][-1] assert last_stat.pnl_balance.amount == expected_stats[ -1].pnl_balance.amount * FVal(0.45)
def test_get_validators_to_query_for_stats(database): db = DBEth2(database) now = ts_now() assert db.get_validators_to_query_for_stats(now) == [] db.add_validators([ Eth2Validator(index=1, public_key='0xfoo1', ownership_proportion=ONE) ]) assert db.get_validators_to_query_for_stats(now) == [(1, 0)] db.add_validator_daily_stats([ ValidatorDailyStats( validator_index=1, timestamp=1607126400, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=ZERO, end_amount=FVal(32), deposits_number=1, amount_deposited=FVal(32), ), ValidatorDailyStats( validator_index=1, timestamp=1607212800, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ) ]) assert db.get_validators_to_query_for_stats(now) == [(1, 1607212800)] # now add a daily stats entry closer than a day in the past and see we don't query anything db.add_validator_daily_stats([ ValidatorDailyStats( validator_index=1, timestamp=now - 3600, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=ZERO, end_amount=FVal(32), deposits_number=1, amount_deposited=FVal(32), ) ]) assert db.get_validators_to_query_for_stats(now) == [] # Now add multiple validators and daily stats and assert on result db.add_validators([ Eth2Validator(index=2, public_key='0xfoo2', ownership_proportion=ONE), Eth2Validator(index=3, public_key='0xfoo3', ownership_proportion=ONE), Eth2Validator(index=4, public_key='0xfoo4', ownership_proportion=ONE), ]) db.add_validator_daily_stats([ ValidatorDailyStats( validator_index=3, timestamp=1607126400, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=ZERO, end_amount=FVal(32), deposits_number=1, amount_deposited=FVal(32), ), ValidatorDailyStats( validator_index=3, timestamp=1617512800, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=4, timestamp=1617512800, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ), ValidatorDailyStats( validator_index=4, timestamp=now - 7200, start_usd_price=FVal(1.55), end_usd_price=FVal(1.55), pnl=ZERO, start_amount=FVal(32), end_amount=FVal(32), ) ]) assert db.get_validators_to_query_for_stats(now) == [(2, 0), (3, 1617512800)] assert db.get_validators_to_query_for_stats(1617512800 + 100000) == [ (2, 0), (3, 1617512800) ]