[Forensics] A quick look at frequency and size of redemptions (KAG)

Yesterday, we collectively found out a quick way of confirming cumulative redemptions.
On the Explorer screen this is described as:

(The sum of all transfer amounts from Hot wallet to Emission or Root account)

In actual fact, it's transfer amounts from Hotwallet to Emission
or flows from Hotwallet to Emission
or in mathematical notation
F (H, E)
where F is the sum of all flows
H, E are the corresponding nodes in the flow-graph.

We found out the sum was
5207555.92

If you'd like to reconfirm that in another linux one-liner (using a slightly different technique fom yesterday's), try:

linux> sed '1d' $KAG_extract | sed 's/\"//g' | awk -F, -v x=$KAG_HotWalletAccount -v y=$KAG_EmissionAccount '( ($4=="payment") && (($13==x) && ($14==y)) ) {printf "%s\t%10.2f\n", $6, $15}' | sort -k1 | cut -f2 | paste -d+ -s | bc -l
5207555.93

\t is to insert a TAB so that sort and cut can work without thinking

How many redemption events have there been?

linux>  sed '1d' $KAG_extract | sed 's/\"//g' | awk -F, -v x=$KAG_HotWalletAccount -v y=$KAG_EmissionAccount '( ($4=="payment") && (($13==x) && ($14==y)) ) {printf "%s\t%10.2f\t%s\n", $6, $15, $7}' | sort -k1 | wc -l
464

464 redemption events in total over just under 4 years

Let's show our work as usual:

linux> first=`sed '1d' $KAG_extract | sed 's/\"//g' | awk -F, -v x=$KAG_HotWalletAccount -v y=$KAG_EmissionAccount '( ($4=="payment") && (($13==x) && ($14==y)) ) {printf "%s\t%10.2f\n", $6, $15}' | sort -k1 | head -1 | cut -f1`
linux> last=`sed '1d' $KAG_extract | sed 's/\"//g' | awk -F, -v x=$KAG_HotWalletAccount -v y=$KAG_EmissionAccount '( ($4=="payment") && (($13==x) && ($14==y)) ) {printf "%s\t%10.2f\n", $6, $15}' | sort -k1 | tail -1 | cut -f1`

linux> echo $first
2019-08-29T04:35:00Z
linux> echo $last 
2023-06-16T17:34:1

date '+%s' -d "$last"
linux> date '+%s' -d "$last"
1686936855

echo $((($(date '+%s' -d "$last") - $(date '+%s' -d "$first")) / (24 * 60 * 60)))
linux> echo $((($(date '+%s' -d "$last") - $(date '+%s' -d "$first")) / (24 * 60 * 60)))
1387

units -o "%4.2f" -t "1387 days" year
linux> units -o "%4.2f" -t "1387 days" year
3.80

linux> bc <<< "scale=2; 1387 / 464"
2.98

1 redemption event every nearly 3 days on average

What do these actually look like?
Let's list the last 30:

linux> sed '1d' $KAG_extract | sed 's/\"//g' | awk -F, -v x=$KAG_HotWalletAccount -v y=$KAG_EmissionAccount '( ($4=="payment") && (($13==x) && ($14==y)) ) {printf "%s\t%10.2f\t%s\n", $6, $15, $7}' | sort -k1 | tail -30
2023-04-05T18:41:38Z       7000.00    a23e562ec5dcf429fa40d0e86cbe2ec80a8a171f197bcc05e9d456d4b86d9510
2023-04-05T19:44:21Z       5000.00    957186288687804306225821d0d800905541c0dfd21e57f8b6588fe5e5dc5d14
2023-04-06T03:12:27Z        964.53    4117b31b859fd4dc4488369ab3748185061d4114c051a014d71603a85ab0b665
2023-04-06T03:45:01Z        200.00    c5839c20d81d1f16c6c9ef5e29cf8b0b40886a7676d77013e7ee8f40a7939131
2023-04-11T15:10:42Z      10000.00    8741323a50df7780de56db3660b126c892f3e1b870ec7e2de9cfdaf605349729
2023-04-12T01:29:24Z      14000.00    e70513c118a909a9832f64c8f3215f9d8ccb4fb2957e960d00db2f0f50799c3a
2023-04-12T14:42:39Z      15000.00    e762943bdcbc5d76f38a4beedab7d0f08cd9e2df60f5c69f7a17039c76b459bc
2023-04-14T01:56:41Z      18000.00    ca5b99bda986daa997d977417dd08e0c570433f9adaa7b0cff4bf0ca6998f9f2
2023-04-24T22:10:52Z       8000.00    b01e0cd3e50782e5d214f34f82bc76df1ba957e0f4c602901b6902a795b2b3d3
2023-04-26T07:37:31Z        800.00    f803cb505d6535736b337010a58ec0be3ce3282e1e5a4078d4e6c6bc75a688a4
2023-05-01T04:53:41Z       8799.10    575f334d123314ae8620805ac05be1a723acdbcf8d704b7ae6708cb87334cfcb
2023-05-01T12:35:53Z       5000.00    40f1cfe684c3eb92027e9d974819475e218b78d8c0a9dc37af4dedf961f37021
2023-05-01T12:39:56Z       5000.00    cdf2921dae96b1b196906b80825954e4766dbaa3a921e04b9a79c9c14299770e
2023-05-02T02:33:27Z       9224.80    73c47558c1c2ac764b5f37ca61d42090ac99c52b68e4b43c05606bd8beca2305
2023-05-04T15:46:50Z      10000.00    3b81c124c3b6af1b68628e6c328d45326bcbbc002515c8543af5c6ac44aa953d
2023-05-04T16:06:56Z      10000.00    20ffd7c9c7288762b3ed2da38bd601c20cdb2083ff949f81b98f4160518f535b
2023-05-04T16:17:35Z      10000.00    74a02cab8e6477e880afe72f29ff9ee2c8bd0b3ce5f9079c26eaec33422f53cf
2023-05-04T19:03:59Z       5000.00    509aa03dcf31c4db1d50754c0a74273f0fa9f13cd8816b255a89f478a5e03098
2023-05-07T22:13:38Z      10000.00    4527386c4e1cb4c62582d513328978d3b31c617010df5d47fab262a41e9effc1
2023-05-08T11:43:54Z      10000.00    854b2133a6288bdd50ea99c3172c20270c17212e2deef5bc7251aff5b0f6d7aa
2023-05-11T10:04:14Z       7386.80    55311f152f04b9a2c797a647f9a9e3ba20d47aeebfcd3a374fae81b30c0c761f
2023-05-18T01:50:51Z        155.51    7f4bc012787ace7109384c6d6508a1b725409adde7f3b20ee6916ffe92ad90b7
2023-05-18T06:03:39Z         40.37    7e0b19463bec4abef421e1e164e820c0fdbb8a93a65347d2a6e30eb16628cb6d
2023-06-05T02:07:20Z        400.00    9595be7bb1b61d2f10a1a259b98170f5f0cd7cc5fcfb57d93a525a2f41f25178
2023-06-08T12:08:41Z      25000.00    2392a89751918e9c4a6b560f11edc3a7a5bc968337fb00b40a48d82679429882
2023-06-08T16:02:55Z      10000.00    bd64f3d1c377b6977f93f411683c228ac38c6f5089c383e8175684be8ef1acd7
2023-06-15T18:10:37Z       7000.00    7dae830af3cc40dc732335b45c7a5c6d33a363f899d566b610a0be14e447781a
2023-06-16T07:13:01Z       5000.00    61c9cd180a0380f13617c6df01dcbc07cc618a328a75232470fe6addf318b14e
2023-06-16T14:38:24Z      15000.00    571be2850ac309a4d75cee7c98cb54a33801284ec390575dad51c5958aea56cf
2023-06-16T17:34:15Z       5000.00    fe15319c1d9dab123a581b3a8a4823b1f92b08b7030e3f2fa2b481377638a2ae

Some days have 'em. Several sometimes.
Some days don't.

To get a closer look at them, here's the usual 2 methods:

Method 1. Hit the CSV flat file from the Derek's Watcher node directly
with the transaction hash (third field listed above)

linux> echo $KAG_extract 
../dat/kag-stellar-export-2023-06-17-T01:34:31-UTC.csv
linux> grep fe15319c1d9dab123a581b3a8a4823b1f92b08b7030e3f2fa2b481377638a2ae $KAG_extract
"113587522608566273","true","GBTYCT2VVWURNU23ZSR3IPSXU6BRWT3ELIOQJAJOKGIHCLLE6YDX4A7E","payment","1","2023-06-16T17:34:15Z","fe15319c1d9dab123a581b3a8a4823b1f92b08b7030e3f2fa2b481377638a2ae","","","","","native","GBTYCT2VVWURNU23ZSR3IPSXU6BRWT3ELIOQJAJOKGIHCLLE6YDX4A7E","GCGTMT2X6NUV6ABEOAOSDI2YQ7FXQOQYKYA7KVZQ5ID67GQU3C6AIUGU","5000.0000000","","","","","",""

Reminder which account is which:

linux> set | grep KAG | grep 'Emission\|Hot'
KAG_EmissionAccount=GCGTMT2X6NUV6ABEOAOSDI2YQ7FXQOQYKYA7KVZQ5ID67GQU3C6AIUGU
KAG_HotWalletAccount=GBTYCT2VVWURNU23ZSR3IPSXU6BRWT3ELIOQJAJOKGIHCLLE6YDX4A7E

Method 2. use the Explorer GUI

https://explorer.kinesis.money/tran...81b3a8a4823b1f92b08b7030e3f2fa2b481377638a2ae

Philosophical aside:
We are quietly building a tool-kit to perform an automated Digital Auditthe same work
Not to be confused with a Physical Audit.

I'll do the same work for KAU when I find the energy.