R calculate aggregate gains or loss using FIFO method

Question

I have two datasets, one with details on stocks purchased called buy and the other with details on stocks sold called sell

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))

The Date is inputted in the format mm-dd-YYYY

My objective is to calculate the aggregate gain/loss on all transactions for the time period included in the data, using the FIFO (First in First Out) method.

I'm trying to do this programmatically using R but have not been successful yet.

Method and desired output

I attempted the calculation manually to demonstrate the FIFO calculation method and the final result that I reached. I have been looking for a way to do this using R, but have not found a successful method yet -

1) 01-07-2020 - Ticker= MSFT - Sold 7 @ $97 - Total = $679
                               FIFO Cost of 7 @ $100 - Total = $700
                               Gain/Loss = -$21

2) 01-01-2020 - Ticker=AMZN - Sold 1 @ $2100 - Total = $2100
                              FIFO Cost of 1 @ $2000 - Total = $2000
                              Gain/Loss = +$100

3) 01-15-2020 - Ticker=DOCU - Sold 10 @ $210 - Total = $2100
                              FIFO Cost of 10 @ $197 - Total = $1970
                              Gain/Loss = +$130

4) 01-20-2020 - Ticker=MSFT - Sold 12 @ $110 - Total = $1320
                              FIFO Cost of 12 @ $[3x$100 + 9x$102] - Total = $1218
                              Gain/Loss = +$102

5) 01-30-2020 - Ticker=AMZN - Sold 3 @ $2050 - Total = $6150
                              FIFO Cost of 3 @ $[1x$2010 + 2x$2011] - Total = $6032
                              Gain/Loss = +$118

6) 04-10-2020 - Ticker=DOCU - Sold 5 @ $205 - Total = $1025
                              FIFO Cost of 5 @ $[2x$197 + 3x$182] - Total = $940
                              Gain/Loss = +$85

7) 04-20-2020 - Ticker=DOCU - Sold 3 @ $225 - Total = $675
                              FIFO Cost of 3 @ $182 - Total = $546
                              Gain/Loss = +$129

Output

Total Loss: -$21 , Total Gains: $664

Net gains are $643

Any help on how to reach this answer programmatically would be greatly appreciated.

Answer 1

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