Help Calculating P/L based on Individual Symbols With FIFO calculations

ONP Nino

New Member
Joined
Apr 2, 2018
Messages
6
Hello everyone,

Just when I thought I had it all worked out With FIFO and Averages after many days, trials and errors,
I started inputting different Symbols and it all Failed on me.

The current FIFO Module calculations works perfectly if I only use same symbol in Column "I" But not when I input Different Symbols.
I Have highlighted in Yellow the should be result, in Orange the Bad result and in Green the Good result.

How can I Calculate The P/L in Column "O" and Average-FIFO in Column "U" based only on Individual Symbol transactions?
I have attempted to find a solution with functions such as Sumproduct, Sumifs and index/match but to no avail.

I believe the Module code needs modifications to accept different symbols
Of perhaps have been writing the attempted formulas incorrectly.

All would be perfect If I could Have the FIFO P/L and FIFO Averages corrected.

Thank you for the help

Cheers
ONP

This is the Code written in the Module
VBA Code:
Function CostOfShares(PresentShares As Range, DescriptionRng As Range, SharesRng As Range, DebitRng As Range)

Dim T As Long, Temp As Long, Amt As Double

Temp = PresentShares.Value

For T = DescriptionRng.Rows.Count To 1 Step -1
    If DescriptionRng.Cells(T, 1) = "BUY" Then          'And SharesRng.Cells(T, 1) < Temp
        If Temp >= SharesRng.Cells(T, 1) Then
        Amt = Amt + DebitRng.Cells(T, 1)
        Temp = Temp - SharesRng.Cells(T, 1)
        Else
        Amt = Amt + (Temp * (DebitRng.Cells(T, 1) / SharesRng.Cells(T, 1)))
        Exit For
        End If
    End If

Next T
CostOfShares = Amt

End Function


Onp MrExcel Forum Symbol FIFO Sample.xlsm
GHIJKLMNOPQRSTU
1BUY And SELL Is CASE SENSITIVE
2
3
4
5
6Doesn’t work well if Different symbols are usedFIFO
7Module FormulaAverage FIFO
8DateDescriptionSymbolSharesPriceDebitCreditBal.P/L
912/21/2020BUYAGNC10015.2401,524.000.001000.00Should Be1524.0015.2400
1012/29/2020BUYNLY10010.0001,000.000.002000.00GOOD2524.0012.6200
1112/30/2020BUYAGNC10014.2401,424.000.003000.00BAD3948.0013.1600
1212/30/2020SELLAGNC10014.0600.001,406.00200-118.002424.0012.1200
131/6/2021SELLNLY5011.0000.00550.0015050.001924.0012.8267
1410/8/2020BUYAGNC10013.4601,346.000.002500.003270.0013.0800
1510/8/2020SELLAGNC10013.1700.001,317.00150105.00-1072058.0013.7200
1610/8/2020BUYAGNC10011.7501,175.000.002500.003233.0012.9320
17
18
19
20
21
22
23GOODWorks Well if all Same SymbolFIFO
24Module FormulaAverage FIFO
25DateDescriptionSymbolSharesPriceDebitCreditBal.P/L
2612/21/2020BUYAGNC10015.2401,524.000.001000.001524.0015.2400
2712/29/2020BUYAGNC10014.0801,408.000.002000.002932.0014.6600
2812/30/2020BUYAGNC10014.2401,424.000.003000.004356.0014.5200
2912/30/2020SELLAGNC10014.0600.001,406.00200-118.002832.0014.1600
301/6/2021SELLAGNC5013.7900.00689.50150-14.502128.0014.1867
3110/8/2020BUYAGNC10013.4601,346.000.002500.003474.0013.8960
3210/8/2020SELLAGNC10013.1700.001,317.00150-99.002058.0013.7200
3310/8/2020BUYAGNC10011.7501,175.000.002500.003233.0012.9320
34
35
36
Sheet1
Cell Formulas
RangeFormula
L26:L33,L9:L16L9=IF(H9="BUY",J9*K9,0)
M26:M33,M9:M16M9=IF(H9="SELL",J9*K9,0)
N9,N26N9=J9
O9:O16O9=T9+SUM(M$9:M9)-SUM(L$9:L9)-SUM(O$8:O8)
N27:N33,N10:N16N10=N9+SUMIFS(J10,H10,"BUY")-SUMIFS(J10,H10,"SELL")
T9:T16T9=CostOfShares(N9,$H$9:$H9,$J$9:$J9,$L$9:$L9)
U26:U33,U9:U16U9=T9/N9
O26:O33O26=T26+SUM(M$26:M26)-SUM(L$26:L26)-SUM(O$25:O25)
T26:T33T26=CostOfShares(N26,$H$26:$H26,$J$26:$J26,$L$26:$L26)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AF:XFD,T15:AD15,A15:R15,A6:F6,A16:AD1048576,A3:G3,L3:AD3,A1:AD2,H6:Q6,A4:Q5,S4:AD6,A7:AD14Cell Value=0textNO
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,127,005
Messages
5,622,136
Members
415,879
Latest member
drumsmasher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top