Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FIFO method calculations
Hello:
I am looking for some help. I have a worksheet of daily stock trades for one individual, in one stock. Ther may be as many as 100 buy trades and 100 sell trades on any given day, but, there are sometimes 3 trades in one day. In any case, I need to do two things: 1- Subtotal the NET number of shares and dollars spent/received on each given day, which I can easily do by using the Data/Subtotal menu selections. However, the work I am doing has to be done on a First In First Out method of Accounting. This means that, for example, if the guy bought 5000 shares on day #1, and sold 3000 shares on day #1, he would have a 2000 share "net carryover". I would like to make this "carryover" amount become the new first "trade" for day #2, and then calculate the buys and sales for day #2. this goes on ad infinitum. I am only given the data in "raw" form, meaning I get the date of the trade, the buy or sell designation, the number of shares, and the price per share. Is there a way to crate a macro, or some other function, so that, at each change in date, the subtotalled amount would automatically add itself to the top of the next days trades, etc... all the way down the sheet? How do I get Excel to automatically insert a row where needed (below each subtotal, which will be where it should place the "carryover" amount)??? Any ideas??? Thanks in advance. JM Attero |
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 | |||
how do I create a fifo formula in exel | Excel Worksheet Functions | |||
Interpolation with non-linear information... FIFO | Excel Discussion (Misc queries) | |||
FIFO Inventory tracking | Excel Programming |