How is weather determined good or bad? It is very objective: Rain is terrible, but maybe not after a dry period. Warm temperatures are fantastic, unless it is too hot, or if it is in combination with too much humidity.
How is the ecosystem of the XRP ledger determined good or bad? If too many people are holding, less trading is moving the price. If everybody is trading, the amount of XRP in actual circulation drives the price down.
Longing for a way to measure the temperature and humidity of the XRP ecosystem, I started a journey to explore monthly figures from the XRP ledger.
The relevant figures
I chose to focus on payment transactions only, and set up a rough SQL snippet to query monthly figures:
WITH ledgersByPeriod AS ( SELECT MIN(ledgers.LedgerIndex) AS firstLedgerIndex, MAX(ledgers.LedgerIndex) AS lastLedgerIndex, DATETIME_TRUNC(CloseTime,MONTH) AS Period FROM xrpledgerdata.fullhistory.ledgers GROUP BY Period ) SELECT Period, … FROM ledgersByPeriod AS a, xrpledgerdata.fullhistory.transactions AS b WHERE b.LedgerIndex >= a.firstLedgerIndex AND b.LedgerIndex <= a.lastLedgerIndex AND … GROUP BY Period, firstLedgerIndex, lastLedgerIndex
Using this framework, I started adding information, summed by the period:
- Number of active wallets (has either sent or received XRP)
- Number of payments made
- Value of all payments made
- Total XRP burned
However, to get a better idea of the overall sentiment of the market, I added a step before grouping all payments by period, an analysis on every wallet.
As touched in my first article An exploration in the XRP ledger with BigQuery, various factors say something about the nature of a wallet, such as if it is a highly distributing wallet or an accumulating wallet. Also, instead of looking at the individual wallets overall time passed, it is now broken down monthly:
- Received amount and number of payments
- Sent amount and number of payments
- How many XRP the wallet has burned by sending payments
- The monthly balance (how much was received versus sent)
To analyse on these numbers, I added some additional information
All wallets included have either received or sent XRP, but the number of transactions can vary from zero to tens of thousands. So on each wallet, I normalised the sent, the received and the total number of payments on a scale from 0 to 1, knowing the maximum count and minimum count in the same period. I did the same for balances to make a balance index.
How much is a wallet interested in keeping the XRP it has received versus sending it? So if a wallet receives 1,435 XRP and sends 1,435 XRP the tendency is 0%, while it is 100% if the wallet received 1,435 XRP but sent 0.
In cases where a wallet has sent far more than they received (used accumulated funds from previous months), the tendency is capped at +/- 100%.
Putting it back together
A random month, October 2015, had 11739 active wallets—so analysing the results wallet by wallet would be an impossible task. So going back to the original intent of having a few simple measurement figures per month, and still represent the data made on an individual wallet basis in a usable way, I summed and averaged the wallet amounts into the final list of information available per month:
- Number of wallets
- Number of transactions
- Amount sent in total
- XRP burned
- Average accumulation/distribution tendency
- Average sent activity index
- Average received activity index
- Average total activity index
- Average total balance index
Moreover, two new fields:
If I took the overall figures per month and calculated a balance (received minus sent amount), it would always amount to 0 (if you send XRP there is always a receiver). However, by doing it on an individual basis, we know how many XRP has been spent or accumulated in every single wallet.
So the sum of all balances is an indicator of how many XRP was used in a month, to send the total amount of XRP.
This is expressed in two ways:
- An actual amount
- A reuse ratio (total amount in period / circulating amount).
Click the charts to open an interactive version.
The first charts are not surprising, comparing it with the trading and price activity on exchanges (and remembering some incidents with exceptionally large fees). Maybe with an exception for June 2018 to November 2018 where the number of payments has increased compared to the number of active wallets staying relatively stable.
The average accumulation/distribution tendency is declining, with some spikes in both directions. In periods with high tendency more people are accumulating (keeping received funds).
The circulating amount and reuse ratio are illustrated in two charts. On the first chart, you can see the total amount of XRP moved in the period, and below how many XRP was used to move that amount.
The second chart shows the ratio. A ratio of 1 means that 1 XRP was used to move 1 XRP, while a ratio of 2 means that 1 XRP was used twice. In other words, the lower the ratio, the more the same XRP is moved around. (Remember this is payments only, and not trade activity).
Indexes and filtering
The indexes are not useful, except for using ranges for a sub-export analysing a subgroup.
To illustrate this, I have tried to make a few charts, filtering out the “extremes”: All wallets with sent-, received- and total activity index above 0.2 and balance index over 0.8 is filtered out. Even though it is only a fraction of the wallets that has been removed, there is a noticable difference in number of payments and moved XRP:
The above chart compares various parameters between the filtered and unfiltered dataset. Also, with the edge-cases gone, the tendency and circulation also appear differently:
As with any charts and numbers, people see different things, and the data presents itself differently, depending on how you cut it.
My goal was to find new ways to measure the “weather” of the XRP ecosystem, and I personally especially like the reuse ratio and accumulation sentiment.
I would love to hear your findings or suggestions for improvements and to get you off the ground running, I have made some data available: