R calculate aggregate gains or loss using FIFO method

A bit messy, but works.

library(dplyr)

# First of all, let's convert date to Date class:
buy$Date <- as.Date(buy$Date, '%m-%d-%Y')
sell$Date <- as.Date(sell$Date, '%m-%d-%Y')

# Empty df that will have results of each transaction:
result <- data.frame()

# For each line in Sell df we are going to run this loop

for (i in 1:nrow(sell)) {
    
    print(sell[i, ])
    # Create a temporary table by filtering 'buy' df to the right Ticker and dates before or equal to Sell operation
    temp <- buy %>%
      filter(Date <= sell$Date[i],
             Ticker == sell$Ticker[i])
    
    j = 1
    sellQ = sell$Quantity[i]
    FIFO = 0
    
    # Running a while loop on temp table, iteratively updating FIFO
    while (sellQ > 0) {
      if (sellQ <= temp$Quantity[j]) {
        temp$Quantity[j] = temp$Quantity[j] - sellQ 
        FIFO = FIFO + (sellQ * temp$Price[j])
        sellQ = 0
      } else {
        FIFO = FIFO + (temp$Quantity[j] * temp$Price[j])
        sellQ = sellQ - temp$Quantity[j]
        temp$Quantity[j] = 0 
        j = j + 1
      }
    }
    
    SoldTotal <- sell$Price[i] * sell$Quantity[i]
    gain_loss <- SoldTotal - FIFO
    
    # Creating output line:
    output <- data.frame(round = i,
                         Ticker = sell$Ticker[i],
                         FIFO = FIFO,
                         SoldTotal = SoldTotal,
                         gain_loss = gain_loss)
    
    # Adding output line to result df:
    result <- rbind(result, output)
    
    # Updating buy table
    buy <- buy %>%
      filter(!TransactionID %in% temp$TransactionID) %>%
      bind_rows(temp) %>%
      arrange(TransactionID) %>%
      filter(Quantity != 0)
    
    print('Completed successfully')
}

Final output:

TotalGain = result %>%
  filter(gain_loss > 0) %>%
  summarise(totalGain = sum(gain_loss)) %>%
  .[[1]]

TotalLoss = result %>%
  filter(gain_loss < 0) %>%
  summarise(totalGain = sum(gain_loss)) %>%
  .[[1]]

NetGains = TotalGain + TotalLoss


print(paste('Total Gain:', TotalGain))
print(paste('Total Loss:', TotalLoss))
print(paste('Net Gains:', NetGains))

print('Details:')
result

Result:

> print(paste('Total Gain:', TotalGain))
[1] "Total Gain: 664"
> print(paste('Total Loss:', TotalLoss))
[1] "Total Loss: -21"
> print(paste('Net Gains:', NetGains))
[1] "Net Gains: 643"
> 
> print('Details:')
[1] "Details:"
> result
  round FIFO SoldTotal gain_loss
1     1  700       679       -21
2     2 1218      1320       102
3     3 2000      2100       100
4     4 6032      6150       118
5     5 1970      2100       130
6     6  940      1025        85
7     7  546       675       129

As per comments, there may be non-integer quantity field in both tables. In this scenario, before proceeding further, a minor tweak is suggested. Let's say the quantity field can be rounded to 2 decimals. Then in that case do this

buy$Quantity <- as.integer(round(buy$Quantity*100))
buy$Price <- buy$Price/100

sell$Price <- sell$Price/100
sell$Quantity <- as.integer(round(sell$Quantity * 100))

Needless to say, if your field is to rounded to three decimals replace 100 by 1000 in above codes. But since the below mentioned approaches use uncount function from tidyr which will create as many rows as quantity is there, so it will multiply rows by further 10 times in this scenario.

Now proceed with either of these approaches.

Approach-1 (tidyr & dplyr) only

library(tidyverse)
buy %>% uncount(Quantity) %>%
  group_by(Ticker) %>%
  mutate(Price = cumsum(Price), 
         Quantity2 = row_number()) %>%
  right_join(sell %>%
               group_by(Ticker) %>%
               mutate(Quantity2 = cumsum(Quantity)), by = c('Ticker' = 'Ticker', 'Quantity2' = 'Quantity2')) %>% 
  mutate(CP = diff(c(0, Price.x))) %>%
  group_by(TransactionID.y) %>%
  summarise(CP = sum(CP),
            SP = sum(Price.y * Quantity),
            Profit = SP - CP)
# A tibble: 7 x 4
  TransactionID.y    CP    SP Profit
            <int> <dbl> <dbl>  <dbl>
1               1   700   679    -21
2               2  1218  1320    102
3               3  2000  2100    100
4               4  6032  6150    118
5               5  1970  2100    130
6               6   940  1025     85
7               7   546   675    129

Approach-2 (using purrr::map_*)

buy = data.frame(TransactionID = c(1:10),
                 Ticker=c(rep('MSFT',4),rep('AMZN',3),rep('DOCU',3)),
                 Date=c(rep('01-01-2020',2),rep('01-14-2020',2),rep('01-01-2020',2),rep('01-14-2020',1),'01-01-2020','03-15-2020','04-06-2020'),
                 Price=c(100,102,102,107,2000,2010,2011,197,182,167),
                 Quantity=c(10,10,5,5,1,1,2,12,15,15))

sell = data.frame(TransactionID=c(1:7),
                  Ticker=c('MSFT','MSFT','AMZN','AMZN','DOCU','DOCU','DOCU'),
                  Date=c('01-07-2020','01-20-2020','01-01-2020','01-30-2020','01-15-2020','04-10-2020','04-20-2020'),
                  Price=c(97,110,2100,2050,210,205,225),
                  Quantity=c(7,12,1,3,10,5,3))

library(tidyverse)
buy %>%
  mutate(Date = as.Date(Date, '%m-%d-%Y'),
         buy_or_sell = 1) %>%
  bind_rows(sell %>%
              mutate(Date = as.Date(Date, '%m-%d-%Y'),
                     buy_or_sell = -1)) %>%
  split(.$Ticker) %>%
  map_dfr(~ .x %>% uncount(Quantity) %>%
            group_by(buy_or_sell) %>%
            mutate(d = row_number()) %>%
            ungroup %>%
            arrange(d, -buy_or_sell) %>%
            mutate(profit = cumsum(buy_or_sell *-1 * Price),
                   d2 = rev(cumsum(rev(rev(cumsum(rev(buy_or_sell == -1))) == 1 & buy_or_sell == -1)))) %>% 
            group_by(d) %>%
            mutate(TransactionID = last(TransactionID)) %>%
            group_by(TransactionID) %>%
            summarise(Ticker = first(Ticker),
                      FIFO = sum(Price * (buy_or_sell == 1) * d2),
                      Sold = sum(Price * (buy_or_sell == -1) * d2), .groups = 'drop')
          ) %>%
  filter(FIFO != 0 | Sold != 0) %>%
  group_by(TransactionID) %>%
  mutate(Gain = Sold - FIFO) %>%
  summarise(across(FIFO:Gain, ~sum(.)))
#> # A tibble: 7 x 4
#>   TransactionID  FIFO  Sold  Gain
#>           <int> <dbl> <dbl> <dbl>
#> 1             1   700   679   -21
#> 2             2  1218  1320   102
#> 3             3  2000  2100   100
#> 4             4  6032  6150   118
#> 5             5  1970  2100   130
#> 6             6   940  1025    85
#> 7             7   546   675   129

^{Created on 2021-05-28 by the reprex package (v2.0.0)}