[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

Manager	CounterParty	B/S	Fund	Equity	Qaunitty	Trade Date	NET Price	Spread	Eff./Reset Date
BTG	BOFA	B	BTG FUND	BNP FP EQUITY	10,773.00	20/08/2018	51.9900000	0.300%	03/10/2018

<tbody>
</tbody>

BTG	CITI	B	BTG FUND	SUZB3 BZ EQUITY	44,500.00	20/09/2018	48.0000000	0.750%	04/10/2018
BTG	CITI	B	BTG FUND	TRPL4 BZ EQUITY	9,400.00	20/08/2018	58.5500000	0.750%	04/10/2018
BTG	CITI	S	BTG FUND	ALFAA MM EQUITY	81,590.00	20/08/2018	23.8800000	0.500%	03/10/2018

<tbody>
</tbody>

NEW TRADES

DATE

FUND

B/S

TICKER

QTY

GROSS PRICE

COMM (BPS)

NET PRICE

CURNCY

RATE

SPREAD

SETTLE DATE

END DATE

RESET FREQ

DEALER

BASE

CASH

CONTA LIQ.

SETTLE DEALER

05/10/2018

EDGE MASTER FUND

B

ALFAA MM EQUITY

5,155.00

48.2272

12

23.2901

MXN

UNWIND

UNWIND

09/10/2018

26/09/2019

Monthly

MORGAN STANLEY

3,197.17

EDMPBMXN

MORGAN STANLEY

05/10/2018

EDGE MASTER FUND

B

ATD/B CN EQUITY

1,625.00

48.2272

2.07

48.2372

USD

US0001M INDEX

0.450%

10/10/2018

27/09/2019

Monthly

GOLDMAN SACHS

Act/360

0.00

-

GOLDMAN SACHS

<tbody>
</tbody>

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