Creating a FIFO table with a series of buy and sell transactions based on date

[lnumz]

Hello.

I am attempting to FIFO a series of buy and sell transactions into a semi-formatted output given there is other data I need to calculate for this. However, FIFO'ing the transactions is what I am after largely.

I've effectively given up on this being possible without the use of VBA.

I need transactions that I have laid out like this (these are all from the same security, I just randomized the data for example's sake so if the values don't seem right, no worries).:

To Become something like this:

As you can see, thus far I have created a semi-calculator-esque idea that will allow me to manually FIFO quite easy (IE it allows me to put the buy/sell in, and then tells me how much of each was used and displays the remainder such that I can carry it over myself and delete the order myself when it is complete) but, these lists often have hundreds of transactions. Any form of advice or help would be greatly appreciated.

Thanks!

 

[braindiesel]

Interesting.
Questions...
1) Top image... are buy and sell on one sheet tab or 2
2) Top image, do you already have a formula calculating Used / remaining? or do you need that as well
3) Ultimate goal is to turn Buy / Sell Data into a separate summary sheet... Yes?
4) Can you copy and paste in the first 5 or so rows of top sheet just to save recreation time?

 

[lnumz]

Interesting.
Questions...
1) Top image... are buy and sell on one sheet tab or 2
2) Top image, do you already have a formula calculating Used / remaining? or do you need that as well
3) Ultimate goal is to turn Buy / Sell Data into a separate summary sheet... Yes?
4) Can you copy and paste in the first 5 or so rows of top sheet just to save recreation time?

1) Buy and sell are on one sheet, I just have the black column for 'design'.

2) I have a formula that calculates used and remaining.

3) Yes.

4) I am not entirely sure what you mean by this, but I certainly could copy and paste the first 5 rows in theory.


For example, the next step in this process that I would do manually is to delete the row with 235828 and put 419246 into the red box, see if that fills the entire buy order quantity and then rinse and repeat with remainders and delete rows as neccesary.

 

[braindiesel]

Re #4... instead of can you I should have said...
Would you please...
copy some of the spreadsheet (5 or so lines) and paste them here so that I do not have to reproduce what you are doing to try and solve this

 

[lnumz]

			235828​
BUY
Date	Amount	Cost (CAD)	EX. Rate	Cost (INR)	CPS (INR)		Used	Remaining
Aug 21/2015	191636​	57789.34​	50.22602​	2902529​	15.15​		191636​	0​
Aug 24/2015	463438​	101254.72​	50.55612​	5119046​	11.05​		44192​	419246​
Aug 26/2015	144313​	229253.3​	49.70179​	11394299​	78.96​		0​	144313​
Aug 26/2015	409230​	12227.7​	49.70179​	607738.6​	1.49​		0​	409230​
Sep 22/2015	145521​	84870.41​	49.62779​	4211931​	28.94​		0​	145521​
Sep 23/2015	66617​	21735.37​	49.62779​	1078678​	16.19​		0​	66617​

"Used" column formula: =MIN(B5,sellm-SUM($H$4:H4)) (where sellm is the cell with = 235828 currently)

SELL
Date	Amount	Proceeds		Used	Remaining
Oct 26/2017	235828​	155395.74​		0​	235828​
Oct 27/2017	493978​	107338.42​		0​	493978​
Oct 30/2017	479935​	461698.96​		0​	479935​
Oct 31/2017	424876​	251839.79​		0​	424876​
Nov 01/2017	462423​	59656.56​		0​	462423​
Nov 01/2017	490745​	264779.57​		0​	490745​

 

[braindiesel]

Perfect... give me a bit but I am working it.

 

[lnumz]

Perfect... give me a bit but I am working it.

Thank you so very much.

 

[braindiesel]

When the items (Buy and sell) are added to your output summary, are the removed from the buy sell sheet, or do I need to keep them?

 

[lnumz]

When the items (Buy and sell) are added to your output summary, are the removed from the buy sell sheet, or do I need to keep them?

They could be removed or kept, no worry either way.

 

[braindiesel]

OK... Here we go...
Sheet1

Backup.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	BUY										SELL
3	Date	Amount	Cost (CAD)	EX. Rate	Cost (INR)	CPS (INR)		Used	Remaining		Date	Amount	Proceeds		Used	Remaining
4	Aug 21/2015	191636	57789.34	50.22602	2902529	15.15		0	191636		Oct 26/2017	235828	155395.7		0	235828
5	Aug 24/2015	463438	101254.7	50.55612	5119046	11.05		0	463438		Oct 27/2017	493978	107338.4		0	493978
6	Aug 26/2015	144313	229253.3	49.70179	11394299	78.96		0	144313		Oct 30/2017	479935	461699		0	479935
7	Aug 26/2015	409230	12227.7	49.70179	607738.6	1.49		0	409230		Oct 31/2017	424876	251839.8		0	424876
8	Sep 22/2015	145521	84870.41	49.62779	4211931	28.94		0	145521		Nov 01/2017	462423	59656.56		0	462423
9	Sep 23/2015	66617	21735.37	49.62779	1078678	16.19		0	66617		Nov 01/2017	490745	264779.6		0	490745
Sheet1

Sheet2

Backup.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Amount	Cost (CAD)	EX. Rate	Cost (INR)	CPS (INR)	Date Sold	Amount Sold	Date	Units sold	Proceeds	Ex. Rate	Proceeds (INR)	Cost	Gain / Loss
Sheet2

and your macro

Sub numberCruncher()

'
    Sheets("Sheet1").Activate ' CHANGE THIS TO YOUR SOURCE SHEET NAME
    myPartial = 0
    Do Until Range("P4").Value = ""
    
    Range("sellm").Value = Range("P4").Value
        
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]<=sellm,RC[-6],RC[8])"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=MIN(RC[-6],sellm-SUM(R4C8:R[-1]C))"
    If ActiveCell.Offset(1, 0).Value <> "" Then Selection.End(xlDown).Select
    Range(Range("H5"), Selection).Select
    If Selection.Rows.Count > 1 Then Selection.FillDown
    Range("I4").Select
    Selection.Formula = "=B4-H4"
    If ActiveCell.Offset(1, 0).Value <> "" Then Selection.End(xlDown).Select
    Range(Range("I4"), Selection).Select
    If Selection.Rows.Count > 1 Then Selection.FillDown
    If myPartial > 0 Then
        Range("I4").Value = myPartial
        Range("H4").Formula = "=IF(" & myPartial & "<=sellm," & myPartial & ",RC[8])"
        Range("I4").Formula = "=" & myPartial & "-H4"
    
    End If
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[1],"">""&0)"
    myRows = Range("G1").Value
    Range("G1").ClearContents
    Range("H:I").Value = Range("H:I").Value
    Range("O4").Formula = "=sum(H:H)"
    Range("P4").Value = Range("P4").Value - Range("O4").Value
    
    Sheets("Sheet2").Activate ' CHANGE THIS TO YOUR DESTINATION SHEET NAME
    Range("I1").Select
    If ActiveCell.Offset(1, 0).Value <> "" Then Selection.End(xlDown).Select
    myDestRow = ActiveCell.Offset(1, 0).Row
    
    Sheets("Sheet1").Activate
    Range("A4").Resize(myRows, 6).Copy
    Sheets("Sheet2").Range("A" & myDestRow).PasteSpecial (xlPasteValues)
    Range("H4").Resize(myRows, 1).Copy
    Sheets("Sheet2").Range("H" & myDestRow).PasteSpecial (xlPasteValues)
    Sheets("Sheet2").Range("J" & myDestRow).PasteSpecial (xlPasteValues)
    Range("K4").Copy
    Sheets("Sheet2").Range("G" & myDestRow).Resize(myRows, 1).PasteSpecial (xlPasteValues)
    Sheets("Sheet2").Range("I" & myDestRow).Resize(myRows + 1, 1).PasteSpecial (xlPasteValues)
     
    Sheets("Sheet2").Activate
    Range("J" & myDestRow + myRows).Select
    If myRows > 1 Then
        Selection.Formula = "=sum(J" & myDestRow & ":J" & Selection.Row - 1 & ")"
        Selection.Value = Selection.Value
    Else
        Selection.Value = Selection.Offset(-1, 0).Value
    End If
    Selection.Offset(0, 1).Value = Sheets("Sheet1").Range("M4").Value
    Selection.Offset(0, 2).Value = 50 'change this to wherever you get this exchange rate from
    Selection.Offset(0, 3).FormulaR1C1 = "=RC[-2]*RC[-1]"
    Sheets("Sheet2").Range("N" & myDestRow).Formula = "=IF(And(Sheet1!I4=0,Sheet1!I4=Sheet1!B4),Sheet1!E4,Sheet1!H4/Sheet1!B4*Sheet1!E4)"
    If myRows > 1 Then
        Sheets("Sheet2").Range("N" & myDestRow).Resize(myRows, 1).Select
        Selection.FillDown
        Range("N" & myDestRow).Offset(myRows, 0).Select
        Selection.Formula = "=sum(N" & myDestRow & ":N" & Selection.Row - 1 & ")"
        Selection.Value = Selection.Value
    Else
        Range("N" & myDestRow).Offset(myRows, 0).Select
        Selection.Value = Selection.Offset(-1, 0).Value
    End If
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("J" & myDestRow + myRows).Resize(1, 6).Select
    Selection.NumberFormat = "#,##0"
    Range("I" & myDestRow + myRows).Resize(1, 7).Select
        
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    Range("A" & myDestRow, "O" & myDestRow + myRows).Select
    Selection.Value = Selection.Value
    
    Sheets("Sheet1").Activate
    Range("I3").Offset(myRows, 0).Select
    If ActiveCell.Value <> 0 Then myRows = myRows - 1
    
    Range("A4:I4").Resize(myRows, 9).Select
    Selection.Delete Shift:=xlUp
    Range("K4:P4").Delete Shift:=xlUp
    If Range("I4").Value <> Range("B4").Value Then myPartial = Range("I4").Value
    Range("P4").Select
    
    Loop
End Sub