Nick321

New Member
Joined
Oct 4, 2019
Messages
1
Hello there,

I actually need help in a simple task of stock balance calculation but due to this task being a daily task and my input files having least 200 entries to work on for this task I am having many difficulties. Kindly help to kind of Automate this task (or at least reducing manual calculation) with the help of anything like formulas, VBA code or any other method.

Task - I have a stock balance file of previous day (Input 1) and transaction file of current day (Input 2) and I have to create and Stock balance file of current day (Output) with FIFO method by adding new purchases of current day to the stock and deducting the sales of current day from the stock with FIFO method.

So let me tell you how my files for this task are.

In below attached sample file -

The Input 1 is stock balance report of previous day (in this example it is of 04/07/2020) which consist of-
  • product name showing all the products held at the end of day of 04/07/2020
  • unique code for each purchase made. This code is given at the time of purchase. It will always be in ascending order in this report.
  • buy date shows when this product was purchased.
  • balance stock (qty) shows quantity of of that product at the end of day of 04/07/2020
  • holding period- this is the time period for which we are holding this security in our stock. It is calculated by subtracting the 'buy date' from the report date (in this case it is like 04/07/2020 - the each buy date). Each purchase has an holding period limit of 90 days i.e after a product is purchased at x date it has to be sold within x+90 days. Hence we have to track holding period for each purchase made and not product wise so for eaxmple, as we see in sample input 1 that product A is repeating in the report but the holding period for it is differing based on the 'buy date' of that product. Holding period is calculated for each date i.e even if sunday is holiday that day will still be counted in holding period so no heed is to paid to any holidays.
Note:-Input 1 is always pre-arranged in the ascending order of Unique code.

In Input 2 we have transactions of the current day (in this case 05/07/2020) i.e products bought and sold on current day. This data is not live we receive this data on the end of the current date so this data is static there will not be any new buy and sell after this report is generated.
  • The product name here is showing product bought and sold on that day. The important thing here is that there is no such rule that the transactions will take place in only in or all the products held by us in previous day. For example, as seen in sample input 2 below there is no transcation for Product C. Also we can see that two new products (E and F) are bought on 05/07/2020.
  • Unique code as said earlier is given to each purchase when it is made. Here sale will also have an unique code. basically each transaction is given unique code which will never repeat.
  • Buy/Sell date will show when the security was bought and sold as this transaction report is of 05/07/2020, this column will have only 05/07/2020 in each cell of this coloumn. As I Generate this report daily this column will have only one date.
  • Quantity shows how much quantity is bought or sold in each transaction.

-This Input 2 is the main culprit. as each day on an average 200 transactions take place so the work to generate the output file takes very long and is prone to several errors.

Note:- Input 2 is always pre-arranged in the ascending order of Unique code.


In Ouput we have our working. This is the file I want to somehow automate or reduce the working in. This output is nothing but an stock balance report of Current day (in this example it is of 05/07/2020). so basically like Input 1. This ouput will become input 1 for the next day's working. This report is created at the very end of the day after the transactions for the current day are stopped.

  • Here again the product code shows products held by us at the end of the current day. In the sample output an important thing to notice is that we have the new purchases of the current day added to the report as we can see each buy transaction of 05/07/2020 shown in input 2 is added at the end of the report one below other.
  • We already know what unique code is. The purpose that it serves here is to guide the addition and subtraction of the current day transactions as this output will always has to be in ascending order of unique code, so in the sample output file you can see that Product E comes before Product F because the unique code of E being lesser than the Unique code of F. By the way these Unique codes for the new purchases are picked up from the transaction report i.e (Input 2)
  • Buy date as explained shows when the product was purchased. This date for the new purchases is taken from Input 2
  • Balance Stock (qty)- now this is where all problems come. This column as we know shows the balance stock held on the current day (in this case 05/07/2020). As we are using the FIFO method the sales that took place on 05/07/2020 will be deducted on FIFO basis from each product. So to find out which purchase was first we use unique code, so in our sample for 'Product A' the purchase of unique code 1 was earlier than the purchase of unique code of 4 so when we deduct the sales we will first deduct from unique code 1 and then from unique code 4 and so on. Similar for sales the sales of unique code 22 will be deducted first for Product A and then sales of unique code 32 will be deducted.
  • Holding Period here again shows for how many days we are holding this product from the 'buy date'. This calculation is very easy as this is done on daily basis, ignoring any holidays. We just have to do +1 for each purchase as the holding period will increase by 1 day every day.
  • Helping columnis just to show you my working it does not form part of the actual output. Here let me explain you what have I done.
    • So as per FIFO method the first purchased product needs to be sold first andmas explained earlier unique code is used to find out which product was purchased first. So let's see what has happened in case of 'Product A'.
      • So in case of Product A as we can derive from Input 1 that the purchases are of unique code 1, 4 and 5 . So as per unique codes the first purchase is of unique code 1 then of code 4 and the of code 5. The same is for sales as per Input 2 the sales for Product A are of unique code 22,32 and 47. So the first sale is of 22 then 32 and then 47.
      • So what I do is I copy and paste Input 1 and then add the new buy deals as per Input 2 one below other in ascending order of unique code then start deducting sales of each product one at a time as per FIFO method.
      • Thus For Product A I took first sale from Input 2 of 1500 and deducted it from first purchase of A which is of 2000. Then as the balance of 500 is remaining of first Purchase I took the second sale deal of Product A from Input 2 which is of 2000. Now as the sale is greater than what has remained of the first purchase of I took only part of that sale i.e 500 and knocked out the first purchase to zero. Then I moved to 2nd Purchase of A which is of 2500 and started deduction sales from it. As there is 1500 remaining of the 2nd sale deal we first deduct that from 2500.so 1000 quantity is still in balance of 2nd purchase so I look for more sales of A as I found third sale of A in Input 2 of 800 I deduct that from that 1000. Now 200 more is remaining in that purchase but there is no new sale for A in Input 2 so the balance of 2nd purchase of Product A becomes 200 and the remaining all Purchases of Product A remain the same as there is no more sale to affect them. Then after this I move to next product and do the same working for all the other products. The balance of the products who does not have sale in Input 2 to affect them stay the same. This is the part that I want to automate. If you can kindly provide any formula or VBA code for this part only will also do a great favor. other things I can do manually. Like I can manually add the new buy deals below in the output. I can do +1 in holding period of output manually. I just cannot do this manual work daily as not only the volume is more but there is also chance to miss an sale transaction which can ruin the full report.
      • After that I add + 1 day to holding period in output file. and then save the workbook.
      • For next day I Copy paste this output file and convert it into Input 1 by deleting the entries which have zero balance. This is an important step as I don't want the Input 1 showing any zero balance entries.
So basically I wan and VBA code to do this all in few clicks or an formula or VBA that at-least does that deduction step automatically.

Note:- I have Input 1 and Input 2 in two different workbooks. and I create the Output in an different Workbook. But it is very flexible from my side. I just wan the Output. Other things can be changed. If you want me to put both inputs in 2 worksheets of the same workbook or in single worksheet one below other or any other way that can be arranged. If the output thrown is in different workbook or on different worksheet of the same workbook or below the both inputs or any other way does not matter. I just need the Output thrown to be somehow automated and error free. Any method that gets the work done will be appreciated.


Thanks again for sparing time for this. Any kind of help will be appreciated..

Sample File ----->

Book1
ABCDEFG
1Input 1 ---->Day end stock and holding period as on04-07-2020
2Product nameUnique codeBuy dateBalance Stock (Qty)Holding Period
3A116-04-2020200079
4B220-04-2020150075
5C329-04-2020300066
6A408-05-2020250057
7A522-05-2020250043
8B622-05-2020100043
9C705-06-202020029
10D823-06-202035011
11
12Input 2 ----->Transaction report on date05-07-2020
13
14Product NameUnique CodeBuy/Sell DateBuy/Sell (B/S)Quantity
15A2205-07-2020S1500
16B2505-07-2020S1500
17A3205-07-2020S2000
18A3605-07-2020B200
19A4705-07-2020S800
20D4805-07-2020B100
21B5205-07-2020S200
22E5905-07-2020B1000
23F6405-07-2020B1000
24
25Output---->Day end stock and holding period as on05-07-2020
26
27Product nameUnique codeBuy dateBalance Stock (Qty)Holding PeriodHelping Coloumn (for balance stock)
28A116-04-20200802000-1500(22)-500(32) = 0
29B220-04-20200761500-1500(25) = 0
30C329-04-2020300067no sale
31A408-05-2020200582500-1500(32)-800(47) = 200
32A522-05-2020250044no sale
33B622-05-2020800441000-200(52) = 800
34C705-06-202020030no sale
35D823-06-202035012no sale
36A3605-07-20202000buy -36
37D4805-07-20201000buy - 48
38E5905-07-202010000buy-59
39F6405-07-202010000buy-64
Sheet1





.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
Hello, It appears you have thought thru your requirements. I am sure you have noticed that no one has offered to help. I am offering friendly advice, I doubt anyone will decide to write your rather complex VBA application. If you chop it up into tiny pieces and try something yourself you might get a nibble.
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
232
Office Version
  1. 365
Platform
  1. Windows
Hello Nick

You have presented us with an interesting, though I'm sure it will also be a challenging problem.

I want to CONGRATULATE you on doing such a wonderful job of presenting your problem. What you gave us is much better than we usually receive from people. You did a great job of giving, in great detail, a written description of what you have, what you do and the end results. Your sample data matches your written description in a very easy way for us to follow, which allows us to see how the numbers connect to each other. This is exactly the kind of information a programmer needs. Give yourself a pat on your back for a great job!

After reading over your description and data, a couple of questions came to my mind that I think anyone working on this problem will need answers to.

1) Your Input #2 file shows all the transactions for the day of 05-07-2020. Now lets move ahead one day until the close of 06-07-2020 and you put that day's transactions into Input #2. My question is, do you first save Input #2 data for 05-07-2020 someplace BEFORE pasting in 06-07-2020, or is Input #2 overwritten?

2) This is similar to question #1 except this applies to your OUTPUT file that you would have created using the closing transactions (File #2) for 05-07-2020. Now lets move ahead one day until the close of 06-07-2020. My question is, do you first save the OUTPUT file for 05-07-2020 BEFORE processing the transaction data for 06-07-2020, or is this OUTPUT file overwritten?

TotallyConfused
 

Watch MrExcel Video

Forum statistics

Threads
1,114,255
Messages
5,546,801
Members
410,759
Latest member
Bufnercash
Top