Forum Discussion
Your thoughts serializing arrays to manage state in excel (by way of inventory tracking)
I haven't been of much value to you, but you have given me something to think about. Essentially what I do is record transactions (in-flows and out-flow) and perform the entire calculation from time zero each time a further transaction occurs. The array formulae are fast and this causes no difficulty at first. The point must come, though, where piling through decades of old data cannot be justified.
That would be where your idea of having persistent data to provide a complete description of the current state would be important. This would, in effect, provide a record of a stock-taking exercise and the focus would be the balances rather than the flows.
Since the MAPλ helper function I have used here is something I only completed recently, I have been presumptuous enough to use your problem to further test the functionality by applying it to a set of FIFO calculations corresponding to the individual Lots. The first image shows the result presented as an array of normalised tables
whilst the second has a grouping option selected (cross-tabs are also possible)
An exercise for the future is to create a persistently stored checkpoint that will support ongoing calculation.
You've been of help to me on lots of occasions, including this one. If the solution strikes you as meh, it is probably me who is missing the bigger picture. Generally speaking my solutions are solving excel problems that amount to less than a minute of compute time (if that) at the cost of dozens of hours of thinking about computer science and how I can shoehorn it into excel. What really gets me is that despite my effort to act as if I am capable of using excel labs to achieve efficiencies much closer to assembly language techniques, I am always forced to confront that Excel is a fully abstracted language whose internal calculations are non-transparent and whose own internal calculation engine is doing with the data/formulas what it likes (and I'm sure it makes better choices than I do).
The problem I solved here is one that vexes the both of us with no good solution. How can we use thunks (or any other lazy evaluation type solution to the array of array problems) as return values and not just keep them wrapped up in the ether and scope bound. Serializing the string is a real possibility, I think, as inelegant as it may be. Given my preference for structured data, this is a legitimate way to have something tantamount to tail recursion happening within a table capable of auto expanding/contracting/updating that is subject to re-ordering and filtering on a whim. It is really the re-sorting of tables of thousands of lines and more than two dozen columns that gives me anxiety.
Also, consider that this method allows you to store an array of potentially thousands of cells in a single cell in a fully resolved form that can instantly be reconverted into a full blown array capable of all of your array functions just by using a simple deserialization formula. I just don't think the excel modules (in excel labs) or the standard excel features were really designed for handling the sorts of arrays I want it to consider for thousands of cells.
Coming from an engineering background I am more used to the idea of formulating the numerical analysis problem and then solving it as efficiently as one can within a single computation. The process model by which transactions are processed one at a time as they arise is less familiar territory for me.
Excel, as a functional programming environment will prevent you from changing the state iteratively as new transactions are processed. The use of a table which expands along with the input data would allow the new state to recorded (possibly within hidden fields) and you have demonstrated that it is workable. I do have concerns that the demand on memory and processing (encoding/decoding) might be heavy if an entire copy of the data structure is held at every step.
On the other hand, repeating a complete recalculation from time zero on each occasion a further transaction is appended will also become unworkable as the data grows in volume. It might well be that some compromise solution is called for in which blocks of transactions are processed before the resulting state is stored in order to provide a checkpoint/restart.
As far as thunks are concerned, I see them as simple in-memory references to previously calculated results. Because of their ephemeral nature, I tend to perform quite large blocks of calculation and write back to the grid only the minimum state data required to make sense of the next timestep (opening balances typically, from which the complete model may be built for that timestep).
Although a table would persist calculated data far better than LET variables, that too is limited. Being formulae, the data only persists until the workbook is closed. You have to be able to afford a complete recalculation on workbook open.
Overall, I think the idea of capturing array data as text within one or more cells of a table has its place. I would aim to store the minimum of data required to restart the calculation and possibly only store it at intervals to provide checkpoint restarts if calculation from step to step turns out to be less resource intensive.
I am aware that I have not provided in-depth support for your idea and have even gone off at a tangent, exploring other ideas. I would have liked to be in a position to offer an expert assessment but in reality my comments are somewhat speculative and there are many points at which I would defer to your opinion.