R code that powers Cryptovoxels charts: https://www.cryptovoxels.com/play?coords=W@6935W,57S

delegator_charts.R

library(ghql)
library(jsonlite)
library(tidyverse)

# connect to endpoint
con = GraphqlClient$new(
  url = "https://api.thegraph.com/subgraphs/name/graphprotocol/graph-network-analytics"
)
# initialize a new query
graphql_request = Query$new()
# make request
graphql_request$query('mydata', '{
  delegatedStakeDailyDatas(where:{delegator:"0x74dbb201ecc0b16934e68377bc13013883d9417b"}, orderBy: dayNumber, orderDirection: desc, first:1000){
    dayNumber
    dayEnd
    delegator{
      id
    }
    indexer{
      id
    }
    stakedTokens
    shareAmount
    personalExchangeRate
    latestIndexerExchangeRate
    unrealizedRewards
    realizedRewards
    originalDelegation
    currentDelegation
  }
}')

# Run query (pull data)
delegations = con$exec(graphql_request$queries$mydata)
# convert results from JSON
delegations = as_tibble(fromJSON(delegations)$data$delegatedStakeDailyDatas)

# Adjust token values
delegations = delegations %>% mutate(stakedTokens = as.numeric(stakedTokens)/10^18,
                                     unrealizedRewards = as.numeric(unrealizedRewards)/10^18,
                                     currentDelegation = as.numeric(currentDelegation)/10^18,
                                     originalDelegation = as.numeric(originalDelegation)/10^18)
# extract ids
delegations$delegator = delegations$delegator$id
delegations$indexer = delegations$indexer$id

# convert timestamp to date
delegations$dayEnd = as.POSIXct(as.numeric(delegations$dayEnd), origin = '1970-01-01')

# shorten indexer ids for legend
delegations_viz = delegations %>% mutate(indexer = paste0(substr(indexer, 1, 6),'...'))

# visualize unrealized rewards
ggplot(delegations_viz, aes(dayEnd, unrealizedRewards, color=indexer)) + 
  geom_point() + 
  geom_line() +
  ggdark::dark_theme_bw() +
  ggtitle('Delegator Unrealized Rewards By Indexer', subtitle=paste0('Delegator: ',delegations_viz$delegator)) +
  ylab('Unrealized Profit (GRT)') +
  xlab('Date')


# calculate monthly profit
delegations_viz %>% 
  group_by(indexer) %>% 
  mutate(dailyProfit = currentDelegation-lead(currentDelegation)) %>% 
  mutate(Month = format(dayEnd, '%Y-%m')) %>% 
  group_by(indexer, Month) %>% 
  summarize(monthlyProfit = sum(dailyProfit, na.rm=T)) %>% 
  ggplot(aes(Month, monthlyProfit, color=indexer, group=indexer)) + 
    geom_point(size=2.5) + 
    geom_line() + 
    ggdark::dark_theme_bw() +
    ggtitle('Delegator Monthly Profit By Indexer', subtitle=paste0('Delegator: ',delegations_viz$delegator)) +
    ylab('Monthly Profit (GRT)')
    

# monthly profit for 1,000 GRT
delegations_viz %>% 
  group_by(indexer) %>% 
  mutate(dailyProfit = currentDelegation-lead(currentDelegation),
         multiplier = currentDelegation/1000, 
         dailyProfitPerThousandGRT=dailyProfit/multiplier) %>% 
  mutate(Month = format(dayEnd, '%Y-%m')) %>% 
  group_by(indexer, Month) %>% 
  summarize(monthlyProfitPerThousandGRT = sum(dailyProfitPerThousandGRT, na.rm=T)) %>% 
  ggplot(aes(Month, monthlyProfitPerThousandGRT, color=indexer, group=indexer)) + 
    geom_point(size=2.5) + 
    geom_line() + 
    ggdark::dark_theme_bw() +
    ggtitle('Delegator Monthly Profit (Per 1,000 GRT) By Indexer', subtitle=paste0('Delegator: ',delegations_viz$delegator)) +
    ylab('Monthly Profit (GRT)')

# monthly ROI
delegations_viz %>% 
  group_by(indexer) %>% 
  mutate(dailyProfit = currentDelegation-lead(currentDelegation),
         multiplier = currentDelegation/1000, 
         dailyProfitPerThousandGRT=dailyProfit/multiplier,
         dailyROI = dailyProfitPerThousandGRT/100) %>% 
  mutate(Month = format(dayEnd, '%Y-%m')) %>% 
  group_by(indexer, Month) %>% 
  summarize(ROI = sum(dailyROI, na.rm=T)) %>% 
  ggplot(aes(Month, ROI, color=indexer, group=indexer)) + 
  geom_point(size=2.5) + 
  geom_line() + 
  ggdark::dark_theme_bw() +
  scale_y_continuous(labels = scales::percent) +
  ggtitle('Delegator Monthly ROI By Indexer', subtitle=paste0('Delegator: ',delegations_viz$delegator)) +
  ylab('ROI (%)')