Analyzing Stock Tradebook With R

Given a year worth of trades, the following R script detects the stocks that were not directly bought. They were either acquired as a part of a bonus, split or IPO.

setwd("/Users/vvb/work/projects/trades")
getwd()

library(data.table)
library(readxl)

trades <- readxl::read_excel("tradebook.xlsx", sheet = "TRADEBOOK")
trades <- trades %>% as.data.table()
head(trades)

findUnbalancedTrades <- function(trades) {
  trades1 <- trades[Type=="S",Qty:=-Qty]
  trades2 <- trades1 %>% group_by(Symbol) %>% summarise(Balance = sum(Qty)) %>% as.data.table()
  noBuyTrades <- trades2[Balance < 0]
  if (nrow(noBuyTrades) == 0) {
    print("Trades are balanced")
  } else {
    print("The following trades are not balanced")
    print(noBuyTrades)
  }
}

findUnbalancedTrades(trades)

Output

> setwd("/Users/vvb/work/projects/trades")
> getwd()
[1] "/Users/vvb/work/projects/trades"
> library(data.table)
> library(readxl)
> trades <- readxl::read_excel("tradebook.xlsx", sheet = "TRADEBOOK")
> trades <- trades %>% as.data.table()
> head(trades)
   Trade date          Trade time Exchange    Symbol Type Qty   Rate         Order no Trade no
1: 23-06-2017 1899-12-31 10:07:37      NSE SUNPHARMA    B   2 542.75 1300000000966218 75365300
2: 23-06-2017 1899-12-31 10:07:37      NSE SUNPHARMA    B  30 542.75 1300000000966218 75365299
3: 23-06-2017 1899-12-31 10:07:37      NSE SUNPHARMA    B  28 542.75 1300000000966218 75365303
4: 23-06-2017 1899-12-31 10:07:37      NSE SUNPHARMA    B   2 542.75 1300000000966218 75365302
5: 23-06-2017 1899-12-31 10:07:37      NSE SUNPHARMA    B  30 542.75 1300000000966218 75365301
6: 27-06-2017 1899-12-31 15:12:07      NSE       TWL    B 500 114.90 1300000003434722 76788334
> findUnbalancedTrades <- function(trades) {
+   trades1 <- trades[Type=="S",Qty:=-Qty]
+   trades2 <- trades1 %>% group_by(Symbol) %>% summarise(Balance = sum(Qty)) %>% as.data.table()
+   noBuyTrades <- trades2[Balance < 0]
+   if (nrow(noBuyTrades) == 0) {
+     print("Trades are balanced")
+   } else {
+     print("The following trades are not balanced")
+     print(noBuyTrades)
+   }
+ }
> findUnbalancedTrades(trades)
[1] "The following trades are not balanced"
       Symbol Balance
1: GODREJAGRO     -32
2:   RELIANCE    -200
>