Exactly how compatible is LibreOffice with Microsoft Office?

I’m thinking specifically of calc/excel.

I want to make two spreadsheets, one for working out my timesheets and figuring out how much time I’ve spent on a job compared to others etc.

Another which deals with my finances. In order to automate these as much as possible I would like to integrate my spreadsheets with Python and build several Macro’s.

Samples of what I would build is a prompting system which reads through a fie of my finances containing each item for the month. If it recognises the item it would automatically place it into a specific row/column/sheet. If not the programme will prompt me about the item if it doesn’t to tell iin order to figure out where to put it (in this case the response would then be stored in the hopes that future items will be remembered). Another would be for my timesheet system. There I’d set up a programme which would ask me for a job number, time spent and the day in which I’m talking about. That would then find the appropriate palce and automatically input the item.

After that the sheets would be set to automatically incorporate the new data into their analysis, updating any statistical tables and graphs I’ve set up.

My home computers are all running Ubuntu and therefore I’m using LibreOffice, however my work computer runs windows and Microsoft Office.

So what I want to know is that, if I build all my spreadhseets in LibreOffice, will I be able to transfer them over into Microsoft Office for work successfully. Or should I build a Microsoft and Libre Office version?

A simple very practical approach - Why don’t you build 1 spreadsheet and try how it works in the other application. You don’t need to develop the very big sheet only test sample with a smaller macro, and maybe a chart.

Between Calc and Excel I always use XLS format not XLSX. because I remember having seen some problems.

In an older version of LIbO (v3 family) I remember some bugs with charts. But now I am running LIbO 4.1.4.2 and have not seen a problem recently.

I never had a macro in my spreadsheet thus don’t know how macros will behave.

I would like to integrate my spreadsheets with Python and build several Macro’s. […] My home computers are all running Ubuntu and therefore I’m using LibreOffice, however my work computer runs windows and Microsoft Office.

As @ROSt53 indicates, for compatibility with LO you are going to have to either:

  • Use the old binary XLS format or the newer OOXML XLSX format on older versions on MS Office.
  • Use ODF v1.2 (rather than the v1.2 Extended used by default) in conjunction with MS Office 2013 as indicated here.

In either case though, macros are likely to be an issue as XLSX will only save VBA macros, while ODS can save LO Basic and VBA[1]. There are evidently tools to allow limited Python[2] manipulation in XLS/X, but you do not indicate what you are considering in this area.

By way of general answer to your title, I have recently written this outline of MS binary vs OOXML support in LO.

[1] Tools > Options… > Load/Save > VBA Properties > indicates whether a copy of the existing VBA macro is preserved or not. Also note this answer about the option required at the head of a VBA macro to allow it to run.

[2] There is no native IDE for Python in LO and no native method of saving Python macros in ODS (the scripts are saved to the user profile under Scripts/python/). There are however some workarounds and extensions available to try and simplify this somewhat (refer this Apache OO forum thread).

@owen - Thanks for the link to your excellent article on compatibility of formats!