Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FIFO: Share calculation in VBA?
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
You are intermixing the FIFO and weighted average methods in your post but
it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message ... 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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Hello Michael, thank you for your reply.
I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
I'm sure you know the principle, but when you intermixed the two, it was
difficult to determine which you really meant as the code used to calculate the gain/loss would be different. Let me chew on this one for a while. "NorTor" wrote in message ... Hello Michael, thank you for your reply. I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Great; I hope you will find a way... :-)
I have tried for very long now, and I feel that I am *almost* there, using do until loops in both tables (sheets), subracting sales from the buy if it is smaller than the buy (talking of quantities), and "emptying" the buy going to the next if it is bigger than the buy / remaining of the buy. Problem I got stuck in, was when the sell was bigger than two buys together (or more than two buys), how to build the calculation for all these buys, in theory n buys. Hope to hear from you, thank you so much! Cheers, -NorTor- On Mon, 13 Dec 2004 16:02:22 -0500, "Michael Malinsky" wrote: I'm sure you know the principle, but when you intermixed the two, it was difficult to determine which you really meant as the code used to calculate the gain/loss would be different. Let me chew on this one for a while. "NorTor" wrote in message .. . Hello Michael, thank you for your reply. I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Hi Nor, Have you solved this problem yet? I am just curious. I would work on
this, but maybe you are done? "NorTor" wrote: Great; I hope you will find a way... :-) I have tried for very long now, and I feel that I am *almost* there, using do until loops in both tables (sheets), subracting sales from the buy if it is smaller than the buy (talking of quantities), and "emptying" the buy going to the next if it is bigger than the buy / remaining of the buy. Problem I got stuck in, was when the sell was bigger than two buys together (or more than two buys), how to build the calculation for all these buys, in theory n buys. Hope to hear from you, thank you so much! Cheers, -NorTor- On Mon, 13 Dec 2004 16:02:22 -0500, "Michael Malinsky" wrote: I'm sure you know the principle, but when you intermixed the two, it was difficult to determine which you really meant as the code used to calculate the gain/loss would be different. Let me chew on this one for a while. "NorTor" wrote in message .. . Hello Michael, thank you for your reply. I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Hi Nor,
One more question, what do you want to do with this FIFO-Avgeraged Cost. when you get it? This is the average cost for the whole days Sales or was your intent to bring it back to the Sell sheet and put it next to each sell, assuming you can have more than one Sell in a day? "David" wrote: Hi Nor, Have you solved this problem yet? I am just curious. I would work on this, but maybe you are done? "NorTor" wrote: Great; I hope you will find a way... :-) I have tried for very long now, and I feel that I am *almost* there, using do until loops in both tables (sheets), subracting sales from the buy if it is smaller than the buy (talking of quantities), and "emptying" the buy going to the next if it is bigger than the buy / remaining of the buy. Problem I got stuck in, was when the sell was bigger than two buys together (or more than two buys), how to build the calculation for all these buys, in theory n buys. Hope to hear from you, thank you so much! Cheers, -NorTor- On Mon, 13 Dec 2004 16:02:22 -0500, "Michael Malinsky" wrote: I'm sure you know the principle, but when you intermixed the two, it was difficult to determine which you really meant as the code used to calculate the gain/loss would be different. Let me chew on this one for a while. "NorTor" wrote in message .. . Hello Michael, thank you for your reply. I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Dear David!
Thank you for your two replys; no, I have not yet solved it, I need to solve it though :-) My purpose is to put back the corresponding cost in a column in the sell sheet (table). This again, will be used for calculating the profit / loss for all sales transactions. There will only be one sell pr day here, so the only reason for the date column in both the sell and buy sheets (tables) is to be able to be sort the transactions historically, of course so that the FIFO principle can be used on the correct transactions. I am not sure, but the only feasible way I can think of, is a do until loop in the sell sheet, with a do until loop inside the buy sheet, to test and subtract the quantities from the first sell compared to the first buy, if the sell is larger in quantity, going to the second buy transaction, continuing this to the n buy transaction. Inside this there must be some calculation that adds buy cost for all quantity that adds up to the sold quantity. And also there must be some other logic if the sell is smaller than the first buy, making sure the remains of the first buy then is used to calculate the buy of the next sell transaction. This explanation might just contribute to confuse, but it is kind of hard to explain... hope it helped though :-) Cheers, -NorTor On Tue, 14 Dec 2004 00:25:01 -0800, "David" wrote: Hi Nor, One more question, what do you want to do with this FIFO-Avgeraged Cost. when you get it? This is the average cost for the whole days Sales or was your intent to bring it back to the Sell sheet and put it next to each sell, assuming you can have more than one Sell in a day? "David" wrote: Hi Nor, Have you solved this problem yet? I am just curious. I would work on this, but maybe you are done? "NorTor" wrote: Great; I hope you will find a way... :-) I have tried for very long now, and I feel that I am *almost* there, using do until loops in both tables (sheets), subracting sales from the buy if it is smaller than the buy (talking of quantities), and "emptying" the buy going to the next if it is bigger than the buy / remaining of the buy. Problem I got stuck in, was when the sell was bigger than two buys together (or more than two buys), how to build the calculation for all these buys, in theory n buys. Hope to hear from you, thank you so much! Cheers, -NorTor- On Mon, 13 Dec 2004 16:02:22 -0500, "Michael Malinsky" wrote: I'm sure you know the principle, but when you intermixed the two, it was difficult to determine which you really meant as the code used to calculate the gain/loss would be different. Let me chew on this one for a while. "NorTor" wrote in message .. . Hello Michael, thank you for your reply. I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
That's the logic. You would either have to record the results on the sheet
or always work from the first buy/sell all the way down each time you ran it. -- Regards, Tom Ogilvy "NorTor" wrote in message ... Dear David! Thank you for your two replys; no, I have not yet solved it, I need to solve it though :-) My purpose is to put back the corresponding cost in a column in the sell sheet (table). This again, will be used for calculating the profit / loss for all sales transactions. There will only be one sell pr day here, so the only reason for the date column in both the sell and buy sheets (tables) is to be able to be sort the transactions historically, of course so that the FIFO principle can be used on the correct transactions. I am not sure, but the only feasible way I can think of, is a do until loop in the sell sheet, with a do until loop inside the buy sheet, to test and subtract the quantities from the first sell compared to the first buy, if the sell is larger in quantity, going to the second buy transaction, continuing this to the n buy transaction. Inside this there must be some calculation that adds buy cost for all quantity that adds up to the sold quantity. And also there must be some other logic if the sell is smaller than the first buy, making sure the remains of the first buy then is used to calculate the buy of the next sell transaction. This explanation might just contribute to confuse, but it is kind of hard to explain... hope it helped though :-) Cheers, -NorTor On Tue, 14 Dec 2004 00:25:01 -0800, "David" wrote: Hi Nor, One more question, what do you want to do with this FIFO-Avgeraged Cost. when you get it? This is the average cost for the whole days Sales or was your intent to bring it back to the Sell sheet and put it next to each sell, assuming you can have more than one Sell in a day? "David" wrote: Hi Nor, Have you solved this problem yet? I am just curious. I would work on this, but maybe you are done? "NorTor" wrote: Great; I hope you will find a way... :-) I have tried for very long now, and I feel that I am *almost* there, using do until loops in both tables (sheets), subracting sales from the buy if it is smaller than the buy (talking of quantities), and "emptying" the buy going to the next if it is bigger than the buy / remaining of the buy. Problem I got stuck in, was when the sell was bigger than two buys together (or more than two buys), how to build the calculation for all these buys, in theory n buys. Hope to hear from you, thank you so much! Cheers, -NorTor- On Mon, 13 Dec 2004 16:02:22 -0500, "Michael Malinsky" wrote: I'm sure you know the principle, but when you intermixed the two, it was difficult to determine which you really meant as the code used to calculate the gain/loss would be different. Let me chew on this one for a while. "NorTor" wrote in message .. . Hello Michael, thank you for your reply. I mean the FIFO, and my example is only to show that the cost price (following the FIFO), must be 'weighted together' by the quantity of the buys that together is the quantity of the sale. But the principle is not the problem, I know that very well, but how to program this to be completely dynamic in Excel VBA. Again thank you for your reply. Regards NorTor On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky" wrote: You are intermixing the FIFO and weighted average methods in your post but it can only be one or the other. If you want to use the FIFO method, the gain on your first sale would be calculated as: 5000 * (11.40-10.50) + 2000 * (11.40-11.20) If you want to use weighted average, you would calculate a weighted average of the total holdings of a particular item and use that to calcluate the gain/loss on the sale. The weighted average would then have to be adjusted whenever you buy/sell that item. Some clarification on this might help. "NorTor" wrote in message .. . 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- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Thank you so much Tom!
I feel I am on the right track here, only a few bits stopping me :-) Is it ok if I post my code here if I have more specific questions on 'what is wrong with my code' kind? Hope to hear from you again. All the best, NorTor On Tue, 14 Dec 2004 07:50:15 -0500, "Tom Ogilvy" wrote: That's the logic. You would either have to record the results on the sheet or always work from the first buy/sell all the way down each time you ran it. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share calculation in VBA?
Don't see any reason why you shouldn't.
-- Regards, Tom Ogilvy "NorTor" wrote in message ... Thank you so much Tom! I feel I am on the right track here, only a few bits stopping me :-) Is it ok if I post my code here if I have more specific questions on 'what is wrong with my code' kind? Hope to hear from you again. All the best, NorTor On Tue, 14 Dec 2004 07:50:15 -0500, "Tom Ogilvy" wrote: That's the logic. You would either have to record the results on the sheet or always work from the first buy/sell all the way down each time you ran it. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
FIFO: Share calculation in VBA?
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" wrote: 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- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
FIFO: Share calculation in VBA?
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 On Tue, 14 Dec 2004 16:41:01 -0800, "David" wrote: 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" wrote: 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can we run FIFO Inventory in excel? | Excel Worksheet Functions | |||
how do I create a fifo formula in exel | Excel Worksheet Functions | |||
Interpolation with non-linear information... FIFO | Excel Discussion (Misc queries) | |||
FIFO method calculations | Excel Programming | |||
FIFO Inventory tracking | Excel Programming |