[R-SIG-Finance] help to create a trading journal

Thu Apr 10 08:40:11 CEST 2025

I work as a trader on crypto currency and want to write a trading
journal (it can be, or not, a trading journal as understood by the PMwR
package[1]). All prices and trades are fetched from Binance with
Binancer package[2].
I have spent now many days scratching my head trying to achieve my goal
in a simple way but couldn't find any satisfying method.
I am looking for help to get a process path, a method. It can be with
PMwR or without.

Here is a tibble with all my needed info:

trading_journal <- structure(list(value = structure(c(1734371999.999,
1734372120, 
1734372240, 1734372360, 1734372480, 1734372600, 1734372720,
1734426090.135, 
1734458399.999, 1734544799.999, 1734553973.032, 1734631199.999, 
1734717599.999, 1734803999.999, 1734890399.999, 1734976799.999, 
1735063199.999, 1735149599.999, 1735235999.999, 1735322399.999, 
1735408799.999, 1735495199.999, 1735581599.999, 1735667999.999, 
1735754399.999, 1735810398.277, 1735810489.902, 1735812883.798, 
1735812912.427, 1735812922.868, 1735812934.674, 1735812945.707, 
1735812955.168, 1735840799.999, 1735927199.999, 1736013599.999, 
1736099999.999, 1736177563.787, 1736177649.381, 1736186399.999, 
1736261991.422, 1736262093.964, 1736272799.999, 1736359199.999, 
1736445599.999, 1736531999.999, 1736618399.999, 1736704799.999, 
1736779190.992, 1736791199.999), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), day = structure(c(20073, 20073, 20073, 20073, 20073, 
20073, 20073, 20074, 20074, 20075, 20075, 20076, 20077, 20078, 
20079, 20080, 20081, 20082, 20083, 20084, 20085, 20086, 20087, 
20088, 20089, 20090, 20090, 20090, 20090, 20090, 20090, 20090, 
20090, 20090, 20091, 20092, 20093, 20094, 20094, 20094, 20095, 
20095, 20095, 20096, 20097, 20098, 20099, 20100, 20101, 20101
), class = "Date"), symbol = c(NA, "FTMUSDC", "SUIUSDC", "ETHUSDC", 
"ENAUSDC", "BTCUSDC", "AAVEUSDC", "BTCUSDC", NA, NA, "BTCUSDC", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "BTCUSDC", 
"ETHUSDC", "AAVEUSDC", "BTCUSDC", "ENAUSDC", "ETHUSDC", "FTMUSDC", 
"SUIUSDC", NA, NA, NA, NA, "ETHUSDC", "ENAUSDC", NA, "AAVEUSDC", 
"FTMUSDC", NA, NA, NA, NA, NA, NA, "SUIUSDC", NA), executed_qty = c(NA,
284270, 80024, 91.8981, 207836, 9.3372107, 1152.058, 0.04811, 
NA, NA, 0.12324, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, -0.06504, -32.0833, 94.408, 0.96557, 17708.79, 4.882, 
22584, 4641.4, NA, NA, NA, NA, -64.6968, 194251.85, NA, -1246.466, 
-306854, NA, NA, NA, NA, NA, NA, -61748.4, NA), price = c(NA, 
1.382, 4.7256, 4038, 1.1855, 107012, 386.55, 107358.81, NA, NA, 
101422.93, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, 96408.23, 3443.981937, 332.340557, 96606.12, 1.043348, 3460.4, 
0.856681, 4.411298, NA, NA, NA, NA, 3698.158383, 1.231695, NA, 
323.031195, 0.732393, NA, NA, NA, NA, NA, NA, 4.368867, NA), 
    cummulative_quote_qty = c(NA, 392861.14, 378161.4144, 371084.5278, 
    246389.578, 999193.5914284, 445328.0199, 5165.0323491, NA, 
    NA, 12499.3618932, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, -6270.3912792, -110494.305684, 31375.60727, 
    93279.9712884, 18476.435418, 16893.6728, 19347.2739, 20474.59783, 
    NA, NA, NA, NA, -239259.013301, 239258.996981, NA, -402647.40174, 
    -224737.6957, NA, NA, NA, NA, NA, NA, -269770.56434, NA), 
    source = c("close", "trade", "trade", "trade", "trade", "trade", 
    "trade", "trade", "close", "close", "trade", "close", "close", 
    "close", "close", "close", "close", "close", "close", "close", 
    "close", "close", "close", "close", "close", "trade", "trade", 
    "trade", "trade", "trade", "trade", "trade", "trade", "close", 
    "close", "close", "close", "trade", "trade", "close", "trade", 
    "trade", "close", "close", "close", "close", "close", "close", 
    "trade", "close"), BTCUSDC_price = c(107112.36, NA, NA, NA, 
    NA, NA, NA, NA, 107517.25, 104630.49, NA, 98692.01, 97172, 
    97559.99, 95555.99, 93260, 98743.99, 98475.99, 95740.01, 
    94156.03, 94860.02, 93788.55, 93672.01, 93972.01, 93942.68, 
    NA, NA, NA, NA, NA, NA, NA, NA, 96673.8, 97966.09, 98004.92, 
    97722.38, NA, NA, 101865.33, NA, NA, 97236.45, 94004.02, 
    92686.72, 95786.01, 94113, 95097.6, NA, 91933.25), SUIUSDC_price =
c(4.7252, 
    NA, NA, NA, NA, NA, NA, NA, 4.6923, 4.7017, NA, 4.2422, 4.4909, 
    4.5497, 4.3416, 4.293, 4.6079, 4.5242, 4.1963, 4.2141, 4.1772, 
    4.123, 4.1906, 4.2085, 4.1817, NA, NA, NA, NA, NA, NA, NA, 
    NA, 4.3391, 4.708, 5.1388, 5.2029, NA, NA, 5.224, NA, NA, 
    4.8313, 4.5976, 4.7354, 5.1894, 4.941, 4.8115, NA, 4.4785
    ), ENAUSDC_price = c(1.1862, NA, NA, NA, NA, NA, NA, NA, 
    1.1412, 1.0928, NA, 1.0256, 1.0704, 1.0716, 1.0395, 1.0255, 
    1.0493, 1.0278, 0.9206, 0.9422, 0.9371, 0.9415, 0.9833, 0.9192, 
    0.9212, NA, NA, NA, NA, NA, NA, NA, NA, 1.0404, 1.2138, 1.2446, 
    1.2394, NA, NA, 1.1984, NA, NA, 1.0946, 0.9606, 0.916, 0.9408, 
    0.8888, 0.8978, NA, 0.7794), AAVEUSDC_price = c(388, NA, 
    NA, NA, NA, NA, NA, NA, 365.68, 373.15, NA, 316.69, 319.8, 
    306.06, 308.15, 352.45, 376.21, 369.33, 334.15, 327.66, 334.95, 
    331.35, 331.56, 312.13, 313.71, NA, NA, NA, NA, NA, NA, NA, 
    NA, 332.95, 348.72, 353, 340.82, NA, NA, 342.41, NA, NA, 
    312.68, 284.99, 283.29, 289.79, 285.09, 292.89, NA, 267.83
    ), ETHUSDC_price = c(4034.74, NA, NA, NA, NA, NA, NA, NA, 
    3975.39, 3879.06, NA, 3474.91, 3436.86, 3380.71, 3313.11, 
    3332.66, 3499.69, 3467.64, 3325.02, 3345.58, 3388.82, 3355.39, 
    3385.8, 3358.42, 3336.65, NA, NA, NA, NA, NA, NA, NA, NA, 
    3447.37, 3573.18, 3633.24, 3627.35, NA, NA, 3674.61, NA, 
    NA, 3441.2, 3262.6, 3250.57, 3314.51, 3269.8, 3286.72, NA, 
    3019.4)), row.names = c(NA, -50L), class = c("tbl_df", "tbl", 
"data.frame"))

Column names are self explanatory. 
Cummulative_quote_qty = executed_qty x price
source is either a trade or closing price (17:59:59 daily, but can be
changed to whatever)
TOKEN_price are prices at closing time

I am looking for a table with daily following informations:
Token with size, token position valuation, sum of token valuation as my
portfolio value. I will then add returns, drawdowns.
I can do this but not when adding the trades I have made (buy more of a
token, partial benefits, position closing). Taking account of portfolio
changes due to trade is my main source of problem. One more difficulty
is when trades are made after closing price. If I want to be right, I
need to add these trades the day after, but it brings more troubles. So
I may accept to enter the trade the day it has been done, even after
17:59:59.

Thank you for any help on how I shall proceed, what are the steps.

[1]https://enricoschumann.net/R/packages/PMwR/manual/PMwR.html#journals
[2]https://cran.r-project.org/web/packages/binancer/binancer.pdf

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 488 bytes
Desc: This is a digitally signed message part
URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20250410/c6ba6167/attachment.sig>