提交: ef67ffd09338425732a8a3ac7ba472ecf6379ce4 - myprojects-hg-reborn (hg)

Commit MetaInfo

• 修订版: ef67ffd09338425732a8a3ac7ba472ecf6379ce4 (tree)
• 时间: 2024-01-18 04:34:42
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

These are new codes to process the transactions.

更改概述

• add: R-codes/calculate_coin_gains.R
• add: R-codes/process_transaction_history.R

差异

diff -r c42caa6e783a -r ef67ffd09338 R-codes/calculate_coin_gains.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/calculate_coin_gains.R	Wed Jan 17 20:34:42 2024 +0100
@@ -0,0 +1,38 @@
+rm(list=ls())
+
+library(tidyverse)
+library(binancer)
+library(janitor)
+library(unnest)
+library(openxlsx)
+
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+
+coin_prices_usdt <- binance_coins_prices(unit = "USDT")|>
+    as_tibble()|>
+    arrange(desc(usd)) |>
+    rename("price_usdt"="usd") |>
+    add_row(symbol="USDT", price_usdt=1.) 
+
+my_initial_prices <- read_excel("./list-transactions/portfolio_to_fix.xlsx")|>
+    rename("initial_price_usdt"="average_price_usdt")
+
+
+
+portfolio_gains_losses <- my_initial_prices |>
+    left_join(y=coin_prices_usdt, by=c("coin"="symbol")) |>
+    mutate(variation=(price_usdt-initial_price_usdt)/initial_price_usdt)
+
+portfolio_output <- portfolio_gains_losses |>
+    select(coin, initial_price_usdt, price_usdt, variation) |>
+    mutate(percentage_variation=paste(round(variation*100, 1), "%", sep="")) |>
+    arrange(desc(variation))
+
+
+save_excel(portfolio_output, "portfolio_gain_losses.xlsx")
+
+
+
+print("So far so good")

diff -r c42caa6e783a -r ef67ffd09338 R-codes/process_transaction_history.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/process_transaction_history.R	Wed Jan 17 20:34:42 2024 +0100
@@ -0,0 +1,456 @@
+rm(list=ls())
+
+library(tidyverse)
+library(janitor)
+library(openxlsx)
+library(stringr)
+library(stringi)
+library(openxlsx)
+library(cryptoQuotes)
+library(broom)
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+
+
+df_ini <- read_all_csv()
+
+df <- df_ini |>
+    clean_data()
+
+save_excel(df, "transaction_history.xlsx")
+
+
+total_deposit <- df|>filter(operation=="Fiat Deposit")|>pull(change)|>sum()
+
+##two way to achieve the same
+
+## beginnings <- df |>
+##     arrange(coin, utc_time) |>
+##     group_by(coin) |>
+##     slice(1) |>
+##     ungroup()
+
+
+beginnings <- df |>
+    arrange(coin, utc_time) |>
+    group_by(coin) |>
+    filter(row_number()==1) |>
+    ungroup()
+
+save_excel(beginnings, "first_transaction_per_coin.xlsx")
+
+
+## see what happened with ADA
+
+test <- df |>
+    find_text_filter("ADA") |>
+    arrange(desc(change))
+
+ada_all <- df |>
+    find_text_filter("ADA")
+
+ada <- test |>
+    slice(1:2)
+
+test <- df |>
+    find_text_filter("TIA") |>
+    arrange(desc(change))
+
+tia <- test |>
+    slice(1:2)
+
+test <- df |>
+    find_text_filter("DOT") |>
+    arrange(desc(change))
+
+dot <- test |>
+    slice(1:3)
+
+## test <- df |>
+##     find_text_filter("ADA") |>
+##     arrange(desc(change))
+
+## ada <- test |>
+##     slice(1:2)
+
+test <- df |>
+    find_text_filter("BTC") |>
+    arrange(desc(change))
+
+btc <- test |>
+    slice(c(7,8,9)) ## to fix
+
+
+test <- df |>
+    find_text_filter("KAVA") |>
+    arrange(desc(change))
+
+
+kava <- test |>
+    slice(1:25) ## to fix
+
+
+test <- df |>
+    find_text_filter("MINA") |>
+    arrange(desc(change))
+
+mina <- test |>
+    slice(1)
+
+
+test <- df |>
+    find_text_filter("BSW") |>
+    arrange(desc(change))
+
+bsw <- test |>
+    slice(1:3)
+
+test <- df |>
+    find_text_filter("ETH") |>
+    arrange(desc(change))
+
+eth <- test |>
+    slice(1)
+
+
+test <- df |>
+    find_text_filter("CTSI") |>
+    arrange(desc(change))
+
+ctsi <- test |>
+    slice(1:2)
+
+
+
+test <- df |>
+    find_text_filter("ACH") |>
+    arrange(desc(change))
+
+ach_all <-  df |>
+    find_text_filter("ACH")
+
+ach_agg  <- ach_all |>
+    group_by(operation, account) |>
+    summarise(total_change=sum(change),
+              number_transactions=n()) |>
+    ungroup()
+
+ach <- ach_all |>
+    filter(operation=="Simple Earn Locked Subscription") |>
+    mutate(change=abs(change))
+
+## ach <- test |>
+##     slice(c(1,2,4,5,7))
+
+
+
+
+test <- df |>
+    find_text_filter("MATIC") |>
+    arrange(desc(change))
+
+
+matic <- test |>
+    slice(1:2)
+
+
+
+
+test <- df |>
+    find_text_filter("ARB") |>
+    arrange(desc(change))
+
+arb <- test |>
+    slice(1)
+
+test <- df |>
+    find_text_filter("AVAX") |>
+    arrange(desc(change))
+
+avax <- test |>
+    slice(1)
+
+
+
+test <- df |>
+    find_text_filter("AAVE") |>
+    arrange(desc(change))
+
+
+aave <- test |>
+    slice(1)
+
+
+
+test <- df |>
+    find_text_filter("AXS") |>
+    arrange(desc(change))
+
+axs <- test |>
+    slice(1:3)
+
+
+
+test <- df |>
+    find_text_filter("NEAR") |>
+    arrange(desc(change))
+
+
+near <- test |>
+    slice(c(1,2,5))
+
+
+test <- df |>
+    find_text_filter("ATOM") |>
+    arrange(desc(change))
+
+atom <- test |>
+    slice(1)
+
+test <- df |>
+    find_text_filter("SKL") |>
+    arrange(desc(change))
+
+skl <-test |>
+    slice(5)
+   
+test <- df |>
+    find_text_filter("LDO") |>
+    arrange(desc(change))
+
+ldo <- test |>
+    slice(1)
+
+test <- df |>
+    find_text_filter("OM") |>
+    arrange(desc(change))
+
+om <- test |>
+    slice(1)
+
+om$change <- om$change-test$change[2]
+
+
+test <- df |>
+    find_text_filter("ALGO") |>
+    arrange(desc(change))
+
+algo <- test |>
+    filter(change== -200) |>
+    mutate(change=-change)
+
+test <- df |>
+    find_text_filter("FLOKI") |>
+    arrange(desc(change))
+
+
+floki_all <-  df |>
+    find_text_filter("FLOKI")
+
+save_excel(floki_all, "floki_transactions.xlsx")
+
+floki <- test |>
+    slice(1:6)
+
+
+test <- df |>
+    find_text_filter("CAKE") |>
+    filter(change>=.4) |>
+    distinct(change, .keep_all=T) |>
+    arrange(utc_time)
+
+
+
+
+cake <- test |>
+    slice(1:8)
+
+
+test <- df |>
+    find_text_filter("LINK") |>
+    arrange(desc(change))
+
+link <- test |>
+    slice(1)
+
+test <- df |>
+    find_text_filter("APE") |>
+    filter(change>=0.4)
+
+ape <- test |>
+    slice(1:5)
+
+
+sol_all <- df |>
+    find_text_filter("SOL") |>
+    arrange(desc(change))
+
+sol_agg <- sol_all |>
+    group_by(operation, account) |>
+    summarise(total_change=sum(change),
+              number_transactions=n()) |>
+    ungroup()
+
+sol <- sol_all |>
+    filter(operation %in% c("Simple Earn Flexible Redemption",
+                            "Simple Earn Locked Rewards",
+                            "Staking Rewards"))
+
+test <- df |>
+    find_text_filter("ONE") |>
+    arrange(desc(change))
+
+one_all <- df |>
+    find_text_filter("ONE") 
+
+
+one <- test |>
+    slice(1)
+
+
+test <- df |>
+    find_text_filter("XRP") |> 
+    arrange(desc(change))
+
+xrp <- test |>
+    slice(1:2)
+
+
+test <- df |>
+    find_text_filter("DODO") |> 
+    arrange(desc(change))
+
+dodo <- test |>
+    slice(1)
+
+
+
+test <- df |>
+    find_text_filter("GLMR") |> 
+    arrange(desc(change))
+
+glmr <- test |>
+    slice(1)
+
+
+test <- df |>
+    find_text_filter("SOL") |> 
+    arrange(desc(abs(change)))
+
+
+df.out <- bind_rows(ada, tia, dot, btc, kava, mina,
+                    bsw, eth,  ctsi,  sol,
+                    matic,
+                    arb, avax, aave, axs, near, atom, skl,
+                    ldo, om, floki, link, one, xrp, dodo, glmr,
+                    ach
+                    ) |>
+    mutate(coin_date=as.character(utc_time)) |>
+    mutate(coin_date=substrLeft(coin_date, 10)) ## |>
+    ## mutate(from=date,
+    ##        to=date) |>
+    ## mutate(pair=paste(coin, "USDT", sep=""))
+
+save_excel(df.out,"ini_portfolio.xlsx")
+
+
+df_sel <- df.out |>
+    group_by(coin, coin_date) |>
+    summarise(total_change=sum(change))  |>
+    ungroup() |>
+    mutate(pair=paste(coin, "USDT", sep=""))
+
+coin_list <- df_sel |>
+    group_by(coin, coin_date) |>
+    group_split()
+    
+    ## select(coin, from, to) |>
+    ## group_by(coin, from, to) |>
+    ## group_split()
+
+
+###############################################################
+
+
+## coins <- df.out |>
+##     pull(coin) |>
+##     su()
+
+## coin_pairs <- paste(coins,"USDT", sep="")
+
+price_fun <- function(x){
+
+    res <- cryptoQuotes::getQuote(
+ ticker   = x$pair, 
+ source   = "binance", 
+ futures  = FALSE, 
+ interval = "1d", 
+ from     = x$coin_date,
+ to = x$coin_date) |>
+     tidy() |>
+     rename("date"="index") |>
+     filter(series=="Close") |>
+     mutate(coin=x$coin,
+            pair=x$pair,
+            amount=x$total_change) |>
+     select(coin, pair,date, amount, series,value) |>
+     rename("exchange_rate_to_usdt"="value")
+
+ return(res)
+ 
+}
+
+
+
+prices <- map(coin_list, price_fun)
+
+df_prices <- prices |>
+    list_to_df() |>
+    mutate(total_value_in_usdt=amount*exchange_rate_to_usdt)
+
+df_prices_agg <- df_prices |>
+    group_by(coin, pair) |>
+    summarise(value_usdt_when_purchased=sum(total_value_in_usdt),
+              total_amount=sum(amount)) |>
+    ungroup() |>
+    mutate(average_price_usdt=value_usdt_when_purchased/total_amount)
+
+save_excel(df_prices_agg, "portfolio_to_fix.xlsx")
+
+#############################################################################
+#############################################################################
+#############################################################################
+#############################################################################
+
+coin_set <- sol_all
+
+
+coin_deposit <- coin_set |>
+    filter(operation=="Deposit")
+
+coin_conversion <- coin_set |>
+    filter(operation==  "Binance Convert"  )
+
+coin_staking_rewards <- coin_set |>
+    filter(operation == "Staking Rewards")
+
+coin_to_bnb <- coin_set |>
+    filter(operation ==   "Small Assets Exchange BNB"  ) |>
+    mutate(change=-abs(change))
+
+coin_simple_rewards <-   coin_set |>
+    filter(operation == "Simple Earn Locked Rewards")
+
+coin_locked_redemption <-   coin_set |>
+    filter(operation == "Simple Earn Locked Redemption")
+
+
+
+
+
+my_coin_hist <- bind_rows(coin_deposit, coin_conversion, coin_staking_rewards,
+                      coin_to_bnb, coin_simple_rewards)
+
+
+print("So far so good")