Calculate Stock

Question

Is it possible calculated stock using R? The formula is stock+purchase-sold. In this case first stock (row1) is 0, rg first result stockB1= 12 - 3 = 9 the second (row1) 9+0-5=4.

    df=read.table(text="
AB1 XB1 AB2 XB2 AB3 XB3
12  3   0   5   3   7
11  35  1   7   2   8
0   10  5   16  5   3",h=T)

stock = read.table(text="
AB1 XB1 STB1 AB2 XB2 STB2 AB3 XB3 STB3
12   3   9    5   3   11   3   7   7
11   35  24   1   7   18   2   8   12  
11   10  1    5   16 -10   5   3   -2",h=T)

Where STB is my request, I not need the total but the partial for each. It can be also in another dataframe.

Answer 1

I'm not sure what form of output you're looking for, but here's an approach that assumes each PB column is a purchase and each SB column is a sale.

My first step is to track the original row for later. Then I reshape the data long, splitting the column into two components after the 2nd character, the first component being PB/SB, the second component being the number of trade. I count PB as increases and SB as reductions, and take the cumulative total for each transaction.

library(tidyverse)
df %>% 
  mutate(row = row_number()) %>%
  pivot_longer(-row, names_to = c("type", "num"), names_sep = 2) %>%
  mutate(net = value * if_else(type == "SB", -1, 1)) %>%
  group_by(row) %>%
  mutate(cuml = cumsum(net)) %>%
  ungroup()

## A tibble: 18 × 6
#     row type  num   value   net  cuml
#   <int> <chr> <chr> <int> <dbl> <dbl>
# 1     1 PB    1        12    12    12
# 2     1 SB    1         3    -3     9
# 3     1 PB    2         0     0     9
# 4     1 SB    2         5    -5     4
# 5     1 PB    3         3     3     7
# 6     1 SB    3         7    -7     0
# 7     2 PB    1        11    11    11
# 8     2 SB    1        35   -35   -24
# 9     2 PB    2         1     1   -23
#10     2 SB    2         7    -7   -30
#11     2 PB    3         2     2   -28
#12     2 SB    3         8    -8   -36
#13     3 PB    1        11    11    11
#14     3 SB    1        10   -10     1
#15     3 PB    2         5     5     6
#16     3 SB    2        16   -16   -10
#17     3 PB    3         5     5    -5
#18     3 SB    3         3    -3    -8

This is almost certainly not the final format you want, but we could use this to create a few outputs.

For instance, we might add

... %>%
  select(row, num, cuml) %>%
  pivot_wider(names_from = num, names_prefix = "trades_", values_from = cuml)

to get something like the original, but just showing the total stock after each pair of trades:

# A tibble: 3 × 4
    row trades_1 trades_2 trades_3
  <int>    <dbl>    <dbl>    <dbl>
1     1        9        4        0
2     2      -24      -30      -36
3     3        1      -10       -8

Answer 2

library(tidyverse)
df=read.table(text="
PB1 SB1 PB2 SB2 PB3 SB3
12   3  0   5   3   7
11  35  1   7   2   8
11   10  5   16  5   3",h=T)
df %>% 
  rowwise() %>% 
  mutate(total = sum(c_across(everything())*c(1,-1)))
#> # A tibble: 3 × 7
#> # Rowwise: 
#>     PB1   SB1   PB2   SB2   PB3   SB3 total
#>   <int> <int> <int> <int> <int> <int> <dbl>
#> 1    12     3     0     5     3     7     0
#> 2    11    35     1     7     2     8   -36
#> 3    11    10     5    16     5     3    -8

Edit 1:

The following pipeline might be what you are looking for:

library(tidyverse)
df=read.table(text="
PB1 SB1 PB2 SB2 PB3 SB3
12   3  0   5   3   7
11  35  1   7   2   8
11   10  5   16  5   3",h=T)

df %>% 
  as_tibble() %>% 
  mutate(stock = row_number()) %>% 
  pivot_longer(
    cols = -stock,
    names_to = c("type", "tr_num"),
    names_pattern = "([P|S]B)([0-9]*)"
  ) %>% 
  group_by(stock) %>% 
  mutate(csum = cumsum(if_else(type == "PB", as.double(value), -1 * value))) %>% 
  group_by(stock, tr_num) %>% 
  group_modify(~ add_row(.x, type = "ST")) %>% 
  mutate(value = if_else(type == "ST", lag(csum), as.double(value))) %>% 
  ungroup() %>% 
  select(-csum) %>% 
  unite("header", c(type, tr_num), sep = "") %>% 
  pivot_wider(names_from = header, values_from = value) %>% 
  select(-stock)
#> # A tibble: 3 × 9
#>     PB1   SB1   ST1   PB2   SB2   ST2   PB3   SB3   ST3
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1    12     3     9     0     5     4     3     7     0
#> 2    11    35   -24     1     7   -30     2     8   -36
#> 3    11    10     1     5    16   -10     5     3    -8