The generalisation of spreadsheets use nowadays sometimes hides the fact that these tools aren’t good enough for every imaginable data manipulation. Sometimes people tweak and twist cells in order to achieve results that are simply beyond the possibilities of a spreadsheet application, no matter how powerful. For these occasions, R is probably more suitable.
Anyway, it’s true that spreadsheets are easy to use…
And with that in mind, I had a CSV file containing stock operations, and I needed to calculate the global FIFO (First In, First Out) profit obtained with sell operations, which is something difficult (impossible?) to build on a plain spreadsheet. I could’ve done it in R, it’s true, but that way may be many people wouldn’t have access to this program, because R has way less spread than Calc or Excel…
As LibreOffice Calc can be programmed with Python, I ended up programming a FIFO profit’s stock sell script, and I must say that it’s been a very straightway process, something that I doubt would be the case with Excel and its tweaked languages (still VBA? c’mon… C#? no!!!, please, that must be a joke). I suppose that the script works also with OpenOffice, but I haven’t checked that.
The script has been written expecting data in some fixed columns, and “sell” and “buy” strings as indications of that operations for the row, but all this can be easily changed modifying the script:
# column and value strings definitions: BEGINNING_OF_DATA = 2 # first row with data ASSET = 'C' # Unique asset id (i.e. USD, EUR, STCKXXXX, etc) TYPE = 'E' # SELL | BUY | other SELL = 'sell' # sell string identifier on TYPE column BUY = 'buy' # buy string identifier on TYPE column PRICE = 'G' FEE = 'I' VOLUME = 'J' PROFIT = 'T' # column of results PROFIT_DESC = 'U' # here the ASSET id will be repeated ASSETS_VOL = 'V' # volume of assets of type ASSET after each buy/sell
So a document suitable for the script’s calculations would look like this one:
Note the problem of FIFO profit calculation: the sell on row 6, with a volume of 6.86, had a FIFO cost of
4x7.5 + 2x7.69 + 0.612x7.4 + 0.248x7.26009
because the first buying ops must be exhausted in order of older to newer, until the volume of the sell is completed. Later buy operations are accumulated after the previous ones, and later sell operations keep exhausting in order the older buy ops with remaining volume.
One benefit of using scripts for accessing the spreadsheet data is that they can be run from Calc or from command line (see more instructions here), and in the latter case, log data can be dumped onto stdout to monitor the process and just be sure that everything is running as expected. In order to run from command line, Calc must have been initiated with a special socket parameter:
$ ./soffice.bin --calc \ --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"
By the way, this can also be done in Windows (you’ll have to find your LibreOffice installation path in both cases…):
C:\Program Files (x86)\LibreOffice 5\program> soffice.exe --calc --accept="socket,host=localhost,port=2002;urp;"
Once you’ve opened the desired Calc document, the script can be run from command line… but using LibreOffice’s python, which comes with unotools integrated so, again, you have to change your working directory to LibreOffice’s program path:
$ ./python ~user/FIFOStockSellProfitCalculator.py
Or in Windows:
C:\Program Files (x86)\LibreOffice 5\program> python.exe C:\FIFOStockSellProfitCalculator.py
If LOG variable has been set to 1:
# print logs to stdout LOG = 1
the script run on the data previously shown would print some interesting numbers to stdout:
connecting to LibreOffice... successfully connected to running LibreOffice buy 2 buy 3 buy 4 buy 5 sell 6 [7.35, 6.86] ASSET9 deque([[Decimal('7.5'), Decimal('4')], [Decimal('7.69'), Decimal('2')], [Decimal('7.4'), Decimal('0.612')], [Decimal('7.26009'), Decimal('6.887')]]) deque([[Decimal('7.26009'), Decimal('6.639')]]) T6= -1.2883 ...
The sell on row 6 indicates a sell volume of 6.86 at a price of 7.35, and the next line shows the previous buy operations available to execute that sell.
Next line shows the remaining buy ops after they’ve been exhausted to cope with the sell op: there remain only the last buy op, and its volume has been reduced from 6.887 to 6.639, that is 6.887-6.639=0.248, as it was previously calculated. In this case, the value that will be indicated in cell T6 will be a negative one: the asset was more valuable in the moment of buying than after this sell, at least by a FIFO calculation.
By an AVERAGE calculation, the loss is just of -0,35 = –(((4*7.5 + 2*7.69 + 0.612*7.4 + 6.887*7.26009) / (4 + 2 + 0.612 + 6.887)) – 7.35)*6.86. On the long term, if sell ops tend to exhaust buy ops, this difference tends to zero, but if this condition doesn’t hold, the difference between FIFO and AVERAGE methods will persist.
The script calculates FIFO profits, with gross and net (gross – total fees) totals, and also shows the volume of assets after each buy or sell operation. In this image of the results after running the script, columns T, U and V have been written by the script, as it has written also the “Fees”, “Gross profit” and “Net profit” labels and data, these latter ones as Calc formulas, as they’re simply column sums and one subtraction. You can download the test csv file and the python script at github.
Note that the script uses python’s Decimal object and not float/double numbers, in order to increase the precision of currency calculations.
In order to run the script from inside Calc (Tools / Macros…), so no socket command line parameter is needed anymore!, the script must be copied to LibreOffice’s python directory. In Windows that would typically be:
C:\Program Files (x86)\LibreOffice 5\share\Scripts\python\
The script can be downloaded from github here.
Note that the script can be embedded into any of your Calc documents using LibreOfficeScriptInsert.py script, just doing this:
$ ./LibreOfficeScriptInsert.py myStocks.ods FIFOStockSellProfitCalculator.py
This will create a “myStocks.with_script.ods” file with the script embedded.
This way it’ll be much easier to execute it whenever data is changed, as it can be selected under Tools / Macros… / Run Macro… just by selecting its name under the Library / thisDocument.ods menu.
Note: I’ve updated the script so now intervals of FIFO and average can be specified in code: specifying an all averages interval will make the script calculates average operations, but it can also start calculating FIFO benefits and the change from some row on to average benefits.