Re: [libreoffice-users] Re: Trial of LibreOffice

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
/

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted