I'm not sure this thread is about my problems but the title sure fits. I have had some real problems with what used to be an Excel 2001 spreadsheet running on a Mac 8500 with which I am now having hardware problems. I am also using Linux these days and I find it a real release from the likes of touch screens and gestures. My first machine was a Control Data 1604 that I programmed in assembly language using a Hollerith card punch. Fortran, was new without any year following the name.
My first spreadsheet ran on a Heathkit H49 which drove a TV receiver and it has grown a bit into Excel 2001 but I did a lot of things that modified the spreadsheet so that it works my way. Apple's Macintosh Programmer's Workshop served as my way to write tools that are now called Macros.
I open my Excel spreadsheet in a Linux shell using the command "librecalc Finance_16.xls".
OpenCalc reads it fine and warns me "Macros may contain viruses. Execution of macros is disabled due to the current macro security setting in Tools - Options - LibreOffice - Security."
I don't yet understand that because I haver used anything but the spreadsheet in MS Office but I really expect that I would have some programming to do to make them work with OpenCalc. I accepted the offering to create a new file that I named Finance_16.ods and the spreadsheet now comes up the way I expect. But it still warns me about Macros! I expected them to disappear. There seems to be no way to attempt execution of any of them. I miss my "sort and find now".
My spreadsheet uses one line to represent a payment or a deposit. The first three columns are dates that are the initiation of the transaction, the clearance date at the bank, and a forecast date which is not, at least yet, a problem. The next column I call "method" and is sometimes "DISC" for a well known credit card but also may be a check number or a flag my macros might use to indicate work yet to be done on the details. The E column indicates which of the bank accounts is involved and might be just "s" for cash operations. Out in J,K,L,M adjustments to balances on the appropriate accounts are made using the value in column F. The code there is carefully done so that rows can be added easily without any need to adjust the parameters. Example, =OFFSET(K614,-1,0)-Amount*BalOp. That "K614" item is taken to mean the row in which the formula resides. and OpenCalc might, and might not, do that the way Excel does. The BalOp is a flag that depends on other columns in the ledger. -1 is a deposit, 0 means no change, and +1 is a payment. Lines that are paid but not closed are treated depending on a Macro setting that identifies what we want to see "today".
One thing I like to do is to log in to my bank and download their idea of changes for a month or less. They like to allow download of data that shows amounts and dates when transactions cleared. The bank likes comma separated ASCII text but in Excel2001 I have to have tab separations. A bit of perl5 fixes that easily but neither seems to work for OpenCalc. I like to use a separate sheet in a workbook to accept the bank data and make appropriate adjustments so that the data fits into the columns above. A copy and paste special makes it easy, in a macro, to get the bank's idea and allows me to insert those lines into the ledger sheet at a point close, in time, to the previously entered checks. I can sort them by the amount of the transaction and quickly change the clear time and, perish the thought, add in whole transactions made by the lady of the house. After the fix I change column A, the date, to blank and an overall sort will take them to the bottom for deletion.
BUT OpenCalc seems to have other ideas.
1: The dates from Discover or the bank have date values as MM/DD/YYYY. When I place them over in the right and allow spreadsheet formulas to move them to the right place they come out `MM/DD/YYYY with the starting apostrophe that tells Calc to leave them alone and never convert to date even when I carefully format the receiving cells to MM/DD which is what I really want. There may be a way but I haven't figured it out yet. The problem is the same for directly reading a CSV file
2: When I add the bank items into a point in the ledger near the current time there seems to be no way to ask for a sort that works only for selected rows. In fact I can make OpenCalc forget about the names I have carefully given to the 4 rows at the top and the rest of the ledger.
3: I need a way to insert the bank lines into the defined range and expect the definition of the range to recognize the number of lines involved. Adding 15, or maybe a miscounted 17, lines to a range is needed and I really don't like that. Perhaps there is a way with macros. . . I did find a couple of suggestions for adding a single row in the middle of a range. But why not a simple ability to paste above a selected line? Excel does it.
4: OpenCalc needs to have a way to accept dates, when explicitly told, from the MM/DD/YYYY stuff that banks use. It also needs to be very clear about the underlying float for days and fractions thereof. I think I'm using 1904 as the zero of time but it's not clear that is always true or if it just remembers what Apple set in Excel. I have not found anywhere in the published information that says 1904 and I'm really on a Linux box that uses 1970.
5: /I do have more but I do hope I can be helpful.
Doug
/
![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
