[R-SIG-Finance] help to create a trading journal
Enrico Schumann
e@ @end|ng |rom enr|co@chum@nn@net
Mon Apr 14 07:29:32 CEST 2025
On Thu, 10 Apr 2025, Arnaud Gaboury writes:
> 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.
Not a complete solution, but computing the positions
and total cumulative P/L [including closed and open
positions] at specific timestamps [such as 1759 every
day], could be done as follows with PMwR. Please note
that I will use a much smaller example than yours.
I suppose you have all trades in a journal, such as the
following one:
library("PMwR")
J <- read.table(text = "
instrument,timestamp,amount,price
A,2025-04-08 15:00:00, 10, 10
A,2025-04-08 16:00:00, -5, 20
B,2025-04-08 22:00:00, 100, 5
", sep = ",", header = TRUE)
J$timestamp <- as.POSIXct(J$timestamp)
J <- as.journal(J)
## instrument timestamp amount price
## 1 A 2025-04-08 15:00:00 10 10
## 2 A 2025-04-08 16:00:00 -5 20
## 3 B 2025-04-08 22:00:00 100 5
##
## 3 transactions
Suppose you want to value your portfolio at 17:59:00 on
8 April and 9 April.
t.valuation <-
as.POSIXct(paste(as.Date("2025-04-08") + 0:1, "17:59:59"))
## [1] "2025-04-08 17:59:59 CEST" "2025-04-09 17:59:59 CEST"
To value possible open positions at these timestamps,
prepare a matrix of valuation prices (each row corresponds
to one timestamp, each column to one asset):
price <- read.table(text = "
A,B
15, 5
15, 10
", sep = ",", header = TRUE)
## A B
## 1 15 5
## 2 15 10
So now you have the ingredients: a journal, a vector of
timestamps when to value, and a matrix of valuation prices
(each row of which corresponds to a valuation timestamp).
You can compute total cumulative P/L, which will include
realized and unrealized P/L:
pl <- pl(J, along.timestamp = t.valuation, vprice = price)
sapply(pl, `[[`, "pl")
## A B
## 2025-04-08 17:59:59 75 0
## 2025-04-09 17:59:59 75 500
For the position, call `position`:
position(J, when = t.valuation)
## A B
## 2025-04-08 17:59:59 5 0
## 2025-04-09 17:59:59 5 100
A longer example is at
https://enricoschumann.net/notes/valuing-positions.html
Hope that helps
Enrico (maintainer of PMwR)
> 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
>
--
Enrico Schumann
Lucerne, Switzerland
https://enricoschumann.net
More information about the R-SIG-Finance
mailing list