[VBA] Impact a Stock of data using FIFO with additional condition

[yorgosnc]

Hi guys, need some help to finish my code to execute FIFO (First In First Out) in stock of data.

So basically I have a big stock of trades (Equity Swaps by the away) and I receive in a daily basis a few number of new trades that can be: a Unwind, meaning, a partial or a full liquidation of an existing deal or a real new deal. So my big problem is for the unwind deals because I have to execute a FIFO impact based on the same counterparty, same equity and in the oldest deal. So I will give an example, in the sheet "Trades" is my new trades for the day, the first line is a UNWIND deal (Possible to see in Column J or K) for the Equity "ALFAA MM Equity" , so what to I need to do is go in my sheet "Swaps" find all swap from the same Equity (Column "E") and Counterparty (Column "B"), find the oldest deal by the trade date, in this case the line 26 (Trade date 20/08 get in the sheet "Trades" the quantity that it's liquidating and return in this specific deal and decrease the quantity by this amount.

When it's not a UNWIND deal, I just need to take the info for the Trades and insert in the next line after the last row in the sheet "Swaps". A few points here:
- It's possible to have a day trade, so in the sheet "trades" will be a new trade and a Unwind trade with the same amount -> In this case I need to exclude them and not impact
- Still in this "day trade matter", it's possible to have a day trade that before I impact my stock, I need to find the net quantity of the trade it self.

Below is the code that I have so far, for the day trade deals the only thing that I did it's to color the lines of possible day trades to analyse by my self but if anyone has an idea how to do this plus the FIFO code, I really appreciate. I was trying to find a match value and set a range, and after that find the oldest one using Redim Preserve. I'm uncertain if it's that best way to pursue this, please be free to suggest another way. Didn't find a way here to post my spreadh sheet I will try to put the layout below:

STOCK DEALS
[TABLE="width: 934"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]CounterParty[/TD]
[TD]B/S[/TD]
[TD]Fund[/TD]
[TD]Equity[/TD]
[TD]Qaunitty[/TD]
[TD]Trade Date[/TD]
[TD]NET Price[/TD]
[TD]Spread[/TD]
[TD]Eff./Reset Date[/TD]
[/TR]
[TR]
[TD]BTG[/TD]
[TD]BOFA[/TD]
[TD]B[/TD]
[TD]BTG FUND[/TD]
[TD]BNP FP EQUITY[/TD]
[TD]10,773.00[/TD]
[TD]20/08/2018[/TD]
[TD]51.9900000[/TD]
[TD]0.300%[/TD]
[TD]03/10/2018[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 934"]
<tbody>[TR]
[TD]BTG[/TD]
[TD]CITI[/TD]
[TD]B[/TD]
[TD]BTG FUND[/TD]
[TD]SUZB3 BZ EQUITY[/TD]
[TD]44,500.00[/TD]
[TD]20/09/2018[/TD]
[TD]48.0000000[/TD]
[TD]0.750%[/TD]
[TD]04/10/2018[/TD]
[/TR]
[TR]
[TD]BTG[/TD]
[TD]CITI[/TD]
[TD]B[/TD]
[TD]BTG FUND[/TD]
[TD]TRPL4 BZ EQUITY[/TD]
[TD]9,400.00[/TD]
[TD]20/08/2018[/TD]
[TD]58.5500000[/TD]
[TD]0.750%[/TD]
[TD]04/10/2018[/TD]
[/TR]
[TR]
[TD]BTG[/TD]
[TD]CITI[/TD]
[TD]S[/TD]
[TD]BTG FUND[/TD]
[TD]ALFAA MM EQUITY[/TD]
[TD]81,590.00[/TD]
[TD]20/08/2018[/TD]
[TD]23.8800000[/TD]
[TD]0.500%[/TD]
[TD]03/10/2018[/TD]
[/TR]
</tbody>[/TABLE]

NEW TRADES
[TABLE="width: 1477"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]FUND[/TD]
[TD] B/S[/TD]
[TD]TICKER[/TD]
[TD]QTY[/TD]
[TD]GROSS PRICE[/TD]
[TD]COMM (BPS)[/TD]
[TD]NET PRICE[/TD]
[TD]CURNCY[/TD]
[TD]RATE[/TD]
[TD]SPREAD[/TD]
[TD]SETTLE DATE[/TD]
[TD]END DATE[/TD]
[TD]RESET FREQ[/TD]
[TD]DEALER[/TD]
[TD]BASE[/TD]
[TD]CASH[/TD]
[TD]CONTA LIQ.[/TD]
[TD]SETTLE DEALER[/TD]
[/TR]
[TR]
[TD]05/10/2018[/TD]
[TD]EDGE MASTER FUND[/TD]
[TD]B[/TD]
[TD]ALFAA MM EQUITY[/TD]
[TD] 5,155.00[/TD]
[TD]48.2272[/TD]
[TD]12[/TD]
[TD]23.2901[/TD]
[TD]MXN[/TD]
[TD]UNWIND[/TD]
[TD]UNWIND[/TD]
[TD]09/10/2018[/TD]
[TD]26/09/2019[/TD]
[TD]Monthly[/TD]
[TD]MORGAN STANLEY[/TD]
[TD][/TD]
[TD]3,197.17[/TD]
[TD]EDMPBMXN[/TD]
[TD]MORGAN STANLEY[/TD]
[/TR]
[TR]
[TD]05/10/2018[/TD]
[TD]EDGE MASTER FUND[/TD]
[TD]B[/TD]
[TD]ATD/B CN EQUITY[/TD]
[TD] 1,625.00[/TD]
[TD]48.2272[/TD]
[TD]2.07[/TD]
[TD]48.2372[/TD]
[TD]USD[/TD]
[TD]US0001M INDEX[/TD]
[TD]0.450%[/TD]
[TD]10/10/2018[/TD]
[TD]27/09/2019[/TD]
[TD]Monthly[/TD]
[TD]GOLDMAN SACHS[/TD]
[TD]Act/360[/TD]
[TD]0.00[/TD]
[TD]-[/TD]
[TD]GOLDMAN SACHS[/TD]
[/TR]
</tbody>[/TABLE]

Sub TesteFIFO()
Dim rngBoletas As Range
Dim rngFundos As Range
Dim rngContraparte As Range
Dim rngEquity As Range
Dim rngAux As Range
Dim rngNovo As Range
Dim lngLinhaPainel As Long
Dim lngLinhaSwaps As Long
Dim varQuantidade As Variant

Application.ScreenUpdating = False
shtTrades.Select
lngLinhaPainel = fUltimaLinha(shtTrades)

''Impact trades in stock
'Loop Trades and identify day trades
shtTrades.Select
Set rngtrades = Range("D2:D" & lngLinhaPainel)
Set rngsides = Range("C2:C" & lngLinhaPainel)
Range("A2:A" & lngLinhaPainel).EntireRow.Interior.Color = RGB(255, 255, 255)
LRowSwap = fUltimaLinha(shtSwaps)

i = 0
For Each Cell In rngtrades
If WorksheetFunction.CountIf(rngtrades, Cell) > 1 Then
    If WorksheetFunction.CountIfs(rngtrades, Cell, rngsides, Cell.Offset(0, -1)) <> WorksheetFunction.CountIf(rngtrades, Cell) Then
    Cell.EntireRow.Interior.Color = RGB(255, 192, 0)
    i = i + 1
    Else
    'Verifica se é UNWIND ou Trade Novo
        'Se o trade for UNWIND faz o FIFO
        If Cell.Offset(0, 6).Value = "UNWIND" Then
        trdequity = Cell.Offset(0, 0)
        trdqtd = Cell.Offset(0, 1)
        trdnetprice = Cell.Offset(0, 4)
        trdcontrprt = Cell.Offset(0, 15)
        ReDim ArrayEquity(1 To 1000) As Variant
        
        shtSwaps.Activate
        CurrentRow = 1
        For r = 2 To LRowSwap
            j = 1
            If Range("E" & CurrentRow).Value = trdequity And Range("B" & CurrentRow).Value = trdcontrprt Then
            ArrayEquity(j) = Range("G" & r).Value
            j = j + 1
            Else
            End If
            ReDim Preserve ArrayEquity(1 To j - 1)
        Next r
        
        Else
        End If
          
End If
End If
Next Cell

'Aviso que há possíveis day trades para serem tratados manualmente
If i > 0 Then
MsgBox "Verificar se os swaps marcados são daytrades e tratar manualmente.", vbExclamation
Else
End If

End Sub