FIFO balance stock - How

[Sai]

Hi everyone, Here, I have a FIFO problem. File attached -
CalcHelp16102021_FIFO.ods (27.4 KB)

LT_Buy_Qty is stock which is more than 365 days old and ST_Buy_Qty is stock which is less than 365 days old. I already computed those values and put in appropriate columns.
SALE_Qty is to be deducted on FIFO basis from LT_Buy_Qty and ST_Buy_Qty.

(a) Total_Sale_Qty for each ISIN should be deducted on FIFO basis first from corresponding ISIN’s LT_Buy_Qty in Column E, till all of the LT_Buy_Qty of that ISIN is exhausted. The result would be shown in Column K.

(b) Balance, if any in Total_Sale_Qty of each ISIN beyond LT_Buy_Qty must be similarly deducted on FIFO basis from corresponding ISIN’s ST_Buy_Qty

(c) If there LT_Buy_Qty is zero Total_Sale_Qty FIFO deduction must be done from ST_Buy_Qty and shown in Column L.

(d) If Total_Sale_Qty for any ISIN is zero, needless to say, corresponding ISIN’s result in Column K and Column L will be LT_Buy_Qty and ST_Buy_Qty respectively.

thank you,

[Villeroy]

On a spreadsheet? Really? There is no professional software for this?

[Sai]

Sir Villeroy, with all respects to your spreadsheet talents, Just because it is free, LibreCalc is NOT Unprofessional.
I just don’t have the expertise to do this, but I guess it is definitely an very simple task for spreadsheet experts. Looking forward to solutions / ideas on how to go about this…

[erAck]

I think Villeroy didn’t mean that Calc would be unprofessional, rather that for these tasks probably specialized software exists. Spreadsheets aren’t always the best tool for all tasks just because something involves numbers. If like you think it is definitely an very simple task for spreadsheet experts then maybe someone will show up and solve it.

[Villeroy]

A spreadsheet is a simplified programming language for non-programmers. A very handy tool for quick and dirty ad-hoc solutions. Therefore you may be able to build up fairly usable tools with the help of a spreadsheet rather than a full blown programming langauge. All these solutions I’ve seen in 30 years have one thing in common: They are incomprehensible and difficult to maintain by anybody except the creator of the spreadsheet tool.
The problem you describe here must have been solved thousands of times on thousands of different spreadsheets. In fact I do not know a single one of them. As a matter of fact, all professional software products dealing with financial transactions are never based on any kind of spreadsheet. At most, they offer some export function to let you do your own scenarios and play games without touching any real data which are stored in some kind of database. Apropos database: What you describe here is 100% about record sets, therefore transfering all data to a database would be the first thing I would do before starting serious development without any spreadsheet.

[mariosv]

I think no macro is needed.
The main problem is not having the data sorted by ISN and date.
By reducing the initial data to the basics, giving the data a defined range, to keep the sorting options, sending the order data to a new place, I think it simplifies the calculation of what is needed.
I attach an example file.
Add new data on the first sheet, and
while standing anywhere in the data, Menu/Data/Sort.
On the second sheet, the data is sorted, making it easier to do the calculations.

CalcHelp16102021_FIFO.ods (84.2 KB)

[esbee51]

You can write a macro in LibreOffice to solve your problem.

[Villeroy]

What makes you sure he can?

[esbee51]

I have done it myself for solving similar problem. I learnt the Basic code for the purpose and used it as I thought it fit. I used to use VBA code ( may not be in an efficient way as an epert) earlier.