FIFO: Share calculation in VBA?

[NorTor]

Hello everybody!


I am stuck in how to solve the following problem in Excel VBA.

Here is the situation;

I have two tables, the first one contains all the 'buy' transactions
of one particular financial share. Every row of the table is one
buy-transaction of the share. The columns gives info on buy-date,
number of shares bought, price per share, and total cost.

The second table contains the same info, but with the 'sales' of the
same particular stock. (Same here, e.g. every row is one transaction,
the columns of every row containing the same information as for the
'buy' table.

Now here is my challenge; I need to find a way to calculate a weighted
average of the buy costs related to every 'sales' row... this is to be
able to calculate profit / loss for every transaction (row) in the
'sales' table. In other words, calculation of profit / loss should be
according to the FIFO (First In First Out) principle.

This is easy of course if there is one sale for every buy, e.g. a buy
of 1000 shares is followed by a sale of 1000 share; then the buy price
of that sale would be the exact price of the corresponding buy.

But in fact the quantities of the 'sale' transaction is totally
independent of the quantities of the 'buy' transactions, the only
restriction is that there at all times can be no larger sale quantity
then the stock of shares in total (no short-selling allowed).

So for instance the buys can be like:

Date Qty. Share price
01.01.02 5,000 10.50
04.01.02 8,000 11.20
09.10.03 4,000 11.40
03.08.04 9,500 11.20

And the sales can be like:
05.08.03 7,000 11.40
10.09.04 19,500 11.60

In this example the cost price for the first sale should be calculated
as follows:

((5000 / 7000) * 10.50 + (2000 / 7000) * 11.20) = 10.70

Reason: Sale of 7,000 "eats" first buy of 5,000 + 2,000 of the next
buy (of 8,000) and corresponding cost price for this sale is hereby
the weighted average of these two factors.

The second sale, the corresponding cost is:
((6000 / 19500) * 11.20) + (4000 / 19,500) * 11.40) +
(9500 / 19500) * 11.20)) = 11.241

Reason is the same: Sale of 19,500 remaining share "eats" the
remaining 6,000 from the second buy, and all other remaining buys.

I need a VBA procedure that can handle these fully flexible buy / sell
structures, and calculate the weighted buy-average and have that
inserted as a new column in the 'sales' table.

Please pretty please help me if you can; I have headaches and
nightmares (well, almost ;-)) trying to solve this myself...



Best regards
-NorTor-

 

[David]

Hi Again Nor,
This sort of works. I tried to put some explanation in the code itself.
Start from the date that you are trying to cost. Sheets set up is like this:

"Sell" sheet - Start from here on a date
Date SellPrice Quantity AvgCost
1/1/2004 $15.00 500 $5,700.00
1/2/2004 $15.00 800 $9,095.00
1/3/2004 $15.00 1100 $12,775.00
1/4/2004 $15.00 1400 $16,306.00

"Buy" sheet -
Date Cost Quanity TotalCost
1/1/2004 $11.40 1000 $11,400.00
1/2/2004 $11.35 1000 $11,350.00
1/3/2004 $11.50 1000 $11,500.00
1/4/2004 $11.75 1000 $11,750.00
1/5/2004 $11.28 1000 $11,280.00

You may be able to tweek it for your purposes. having some data on the buy
side might help the most. But take a look at the notes below.

Sub Macro1()
'Does not work on the first Sell bid, have to start on the 2nd,
ReturnAddress = ActiveCell.Address
ThisDate = ActiveCell.Value
ActiveCell.FormulaR1C1 = "=SUM(R[-" & (ActiveCell.Row - 2) & "]C[2]:RC[2])"
Selection.Style = "Comma"
CumTotal = ActiveCell.Value
ActiveCell.Value = ThisDate
Selection.NumberFormat = "m/d/yyy"
QSold = ActiveCell.Offset(0, 2).Value
Sheets("Buy").Select
Range("C2").Select
BuyQ = ActiveCell.Value
Do Until BuyQ >= CumTotal
ActiveCell.Offset(1, 0).Select
BuyQ = BuyQ + ActiveCell.Value
Loop
'Balance Prior Bin
BalPriorBin = BuyQ - CumTotal
COGSPriorBin = BalPriorBin * ActiveCell.Offset(-1, -1).Value
Balance = QSold - BalPriorBin 'Balance left to cost
If Balance <= ActiveCell.Value Then
'All balance costed, 0 balance left TotalCOGS calc
COGSThisBin = Balance * ActiveCell.Offset(0, -1).Value
Else
'Use this bin up and go to next bin with balance
COGSThisBin = ActiveCell.Value * ActiveCell.Offset(0, -1).Value
Balance = Balance - ActiveCell.Value
End If
'From this point forward the code can be copied, but need
'information on the purchase to determin the number to times
'to create new COGSThisBin#
ActiveCell.Offset(1, 0).Select
If Balance <= ActiveCell.Value Then
'All balance costed, 0 balance left TotalCOGS calc
COGSThisBin1 = Balance * ActiveCell.Offset(0, -1).Value
Balance = 0
Else
'Use this bin up and go to next bin with balance
COGSThisBin1 = ActiveCell.Value * ActiveCell.Offset(0, -1).Value
Balance = Balance - ActiveCell.Value
End If
Stop
TotalCOGS = COGSPriorBin + COGSThisBin + COGSThisBin1
'This could be up to any number of + COGSThisBin#, but I think it
'needs to be limited. Tried to put it in an array, but could not
Sheets("Sell").Select
Range(ReturnAddress).Activate
ActiveCell.Offset(0, 3).Value = TotalCOGS
End Sub

 

[nortor]

Thank you so much David, sorry for the very late reply; we have lost a
relative since my last post here.

I will test things out this weekend, and let you know how it goes.


Best regards
NorTor

Hi Again Nor,
This sort of works. I tried to put some explanation in the code itself.
Start from the date that you are trying to cost. Sheets set up is like this:

"Sell" sheet - Start from here on a date
Date SellPrice Quantity AvgCost
1/1/2004 $15.00 500 $5,700.00
1/2/2004 $15.00 800 $9,095.00
1/3/2004 $15.00 1100 $12,775.00
1/4/2004 $15.00 1400 $16,306.00

"Buy" sheet -
Date Cost Quanity TotalCost
1/1/2004 $11.40 1000 $11,400.00
1/2/2004 $11.35 1000 $11,350.00
1/3/2004 $11.50 1000 $11,500.00
1/4/2004 $11.75 1000 $11,750.00
1/5/2004 $11.28 1000 $11,280.00

You may be able to tweek it for your purposes. having some data on the buy
side might help the most. But take a look at the notes below.

Sub Macro1()
'Does not work on the first Sell bid, have to start on the 2nd,
ReturnAddress = ActiveCell.Address
ThisDate = ActiveCell.Value
ActiveCell.FormulaR1C1 = "=SUM(R[-" & (ActiveCell.Row - 2) & "]C[2]:RC[2])"
Selection.Style = "Comma"
CumTotal = ActiveCell.Value
ActiveCell.Value = ThisDate
Selection.NumberFormat = "m/d/yyy"
QSold = ActiveCell.Offset(0, 2).Value
Sheets("Buy").Select
Range("C2").Select
BuyQ = ActiveCell.Value
Do Until BuyQ >= CumTotal
ActiveCell.Offset(1, 0).Select
BuyQ = BuyQ + ActiveCell.Value
Loop
'Balance Prior Bin
BalPriorBin = BuyQ - CumTotal
COGSPriorBin = BalPriorBin * ActiveCell.Offset(-1, -1).Value
Balance = QSold - BalPriorBin 'Balance left to cost
If Balance <= ActiveCell.Value Then
'All balance costed, 0 balance left TotalCOGS calc
COGSThisBin = Balance * ActiveCell.Offset(0, -1).Value
Else
'Use this bin up and go to next bin with balance
COGSThisBin = ActiveCell.Value * ActiveCell.Offset(0, -1).Value
Balance = Balance - ActiveCell.Value
End If
'From this point forward the code can be copied, but need
'information on the purchase to determin the number to times
'to create new COGSThisBin#
ActiveCell.Offset(1, 0).Select
If Balance <= ActiveCell.Value Then
'All balance costed, 0 balance left TotalCOGS calc
COGSThisBin1 = Balance * ActiveCell.Offset(0, -1).Value
Balance = 0
Else
'Use this bin up and go to next bin with balance
COGSThisBin1 = ActiveCell.Value * ActiveCell.Offset(0, -1).Value
Balance = Balance - ActiveCell.Value
End If
Stop
TotalCOGS = COGSPriorBin + COGSThisBin + COGSThisBin1
'This could be up to any number of + COGSThisBin#, but I think it
'needs to be limited. Tried to put it in an array, but could not
Sheets("Sell").Select
Range(ReturnAddress).Activate
ActiveCell.Offset(0, 3).Value = TotalCOGS
End Sub

Hello everybody!


I am stuck in how to solve the following problem in Excel VBA.

Here is the situation;

I have two tables, the first one contains all the 'buy' transactions
of one particular financial share. Every row of the table is one
buy-transaction of the share. The columns gives info on buy-date,
number of shares bought, price per share, and total cost.

The second table contains the same info, but with the 'sales' of the
same particular stock. (Same here, e.g. every row is one transaction,
the columns of every row containing the same information as for the
'buy' table.

Now here is my challenge; I need to find a way to calculate a weighted
average of the buy costs related to every 'sales' row... this is to be
able to calculate profit / loss for every transaction (row) in the
'sales' table. In other words, calculation of profit / loss should be
according to the FIFO (First In First Out) principle.

This is easy of course if there is one sale for every buy, e.g. a buy
of 1000 shares is followed by a sale of 1000 share; then the buy price
of that sale would be the exact price of the corresponding buy.

But in fact the quantities of the 'sale' transaction is totally
independent of the quantities of the 'buy' transactions, the only
restriction is that there at all times can be no larger sale quantity
then the stock of shares in total (no short-selling allowed).

So for instance the buys can be like:

Date Qty. Share price
01.01.02 5,000 10.50
04.01.02 8,000 11.20
09.10.03 4,000 11.40
03.08.04 9,500 11.20

And the sales can be like:
05.08.03 7,000 11.40
10.09.04 19,500 11.60

In this example the cost price for the first sale should be calculated
as follows:

((5000 / 7000) * 10.50 + (2000 / 7000) * 11.20) = 10.70

Reason: Sale of 7,000 "eats" first buy of 5,000 + 2,000 of the next
buy (of 8,000) and corresponding cost price for this sale is hereby
the weighted average of these two factors.

The second sale, the corresponding cost is:
((6000 / 19500) * 11.20) + (4000 / 19,500) * 11.40) +
(9500 / 19500) * 11.20)) = 11.241

Reason is the same: Sale of 19,500 remaining share "eats" the
remaining 6,000 from the second buy, and all other remaining buys.

I need a VBA procedure that can handle these fully flexible buy / sell
structures, and calculate the weighted buy-average and have that
inserted as a new column in the 'sales' table.

Please pretty please help me if you can; I have headaches and
nightmares (well, almost ;-)) trying to solve this myself...



Best regards
-NorTor-