problems opening a web-downloaded Excel spreadsheet in Neo

[NeoPhyte]

Greetings again to you wonderful NeoOffice helpers and troubleshooters. We're delighted to report back that thanks to your guidance and help we have resolved one exporting problem with Word compatibility ... we were trying to export NeoOffice 2007 version into a MS Office Word for Mac 2004 trial version and it couldn't recognise what we were sending it.

Now we're foraying into spreadsheets, trying to open Excel spreadsheets and we are experiencing some anomalies. As with the Word experiment, we haven't had an opportunity to learn how to use Neo Spreadsheets so its likely we're just needing some guidance as to how to do this. Once we know that it can be done as the software stands currently, the immediate current pressure to buy MS software to get the job done will be removed and we will progress to learning competency in Neo applications.

There is one Loan Amortisation Schedule downloaded off the web from a financial website to save people from having to spend the time preparing their own ... all that one needs to do is put in one's own figures into the pre-formed Loan Amortisation Schedule and recalculate to get their own results but the downloaded Schedule comes as a full Excel spreadsheet complete with its own figures already there as an example if this makes sense.

This Loan Amortization Schedule can be downloaded from http://www.vertex42.com/ExcelTemplates/loan-amortization-schedule.html if anybody wants to see what I was trying to explain, or wants to see if they have more luck than we have had. We have no idea what version of what software this was originally done in and probably no way of finding out either.

This downloadeded Excel document opened perfectly in the MS Office for Mac Excel Trial Version 2004 software but is creating problems when we open it up in Neo Spreadsheets, unlike the other Excel spreadsheets we were able to successfully open up as All Files *.*

Nearly all the cells in the Loan Amortisation Schedule are coming up in Neo with “# NAME?” and not with the formula nor with the calculation. This happens when I am in Neo Spreadsheets, File, Open and then opening the Excel Spreadsheet as all files *.*, as spreadsheets, as MS Excel 4.x-5.0/95 (*.xls, * . xlx), as MS Excel 97/2000/XP (.xls, *.xlw), as MS Excel 97/2000/XP Template (*.xlt).

If I try and open this file as a NeoOffice 1.1 Spreadsheet (*.xsc) or NeoOffice Spreadsheet Template (*.stc) or Open Document Spreadsheet (*.ods) , each time I get the same Net Office 2.2 General Error! General Input/Output Error! response. I'm assuming this is because of the incompatibility between 1.1 and 2.2 but there is no option to open the file as NeoOffice Spreadsheet 2.2. Is there a NeoOffice Spreadsheet 2.2 about to be made available to be open into? What suggestions do you all have please as I've tried everything I can imagine and still am not making any progress on this one.

Also, how do we show on the Neo Spreadsheets toolbar the equivalent Excel Auditing icons please. I have found Trace Precedents for example under Tools-Detective-Trace Precedents while under Toolbars there is no way to add these Auditing functions onto the visible toolbar.

Thanks again for your help and looking forward to your much appreciated help in solving this.

[jgd]

When you click on #NAME you can see the function displayed in the function bar.

The problem is that these are Excel functions and not NeoOffice Calc functions. So you get the error message #NAME.

You'll have to recreate all these functions, and it can be a lot tricky

Jacqueline

[NeoPhyte]

Thanks Jacqueline for your feedback. It sounds kind of worrisome, any other suggestions as to how to get around this? I would have thought that all Excel functions would have been a problem, not just these ones specifically that are in this spreadsheet.

Does anybody know if running NeoOffice 2.2 and trying to open Excel spreadsheets in a 1.1 format might also be a problem, and if so, is this on the drawing board yet to be addressed?

Thanks again and looking fwd to any further ways of getting around laboriously re-writing the formulas.

[pluby]

[jgd]

I "explored" the loan-amortization-schedule document a bit:

"Rate per period" displays #NAME? because its content is =D6/periods_per_year, and periods_per_year is never defined in the spreadsheet (I should say I did not see any definition). In this document the frequency of payment is set on Monthly, so you have to replace periods_per_year with 12 in the formula bar. Then you get 0.625% in H5.

In this case "nper" will be equal to 12*D7 because Term of Loan in Years = 1.5 is in D7.

In D11, replace "rate" with H5, "nper" with 12*1.5 and "loan_amount" with D5. You get Payment (per period) = $706.95 in D11.

Then in A17 replace "nper", with 12*$D$7 you get A17=1. Fill the column by dragging the cursor. The range A17:A34 shows the 18 periods of payment. The rest of the column is empty, which is normal.

If I'm not wrong (not sure ), you have to replace

loan_amount with $D$5

term with $D$7

fpdate with $D$8

periods_per_year with 12, 1, 2, 4 , etc. depending on the value of the list box, Monthly, Annualy, Semi-Annually, Quartely, etc.

nper with $D$7*12 if Frequency of Payment is equal to Monthly, $D$7*4 if Frequency of Payment is equal to Quartely, etc.

Don't forget the $ because the references must be absolute ones.
I don't know if all these replacements can be done automatically, specifically I'd like to find a way to replace automatically the "periods_per_year" with the appropriate value. I guess that you should not have to do that by hand, but???

Jacqueline

[jgd]

OK. I found some enhancements.

Click in D5 (the cell which shows the loan amount), go to Insert > Names > Define
In the window which appears, click on loan_amount, then on OK.

Click in D6 and Insert > Names > Define, choose term and click OK

Idem D8 = fpdate, and H5 = rate.

Two (at least) problems remain: how to translate the value of D9 into number (without a lot of work ), and where are the periods_per_year and nper values stored?

Jacqueline

[Lorinda]

Do you have to use that particular document to do your loan calculations?

If not, you might have better luck with the "Calculator for Loans and Mortgages" template available on the OpenOffice.org site. It is written specifically for OpenOffice.org (the code upon which NeoOffice is based), so it is in a Neo native file format. I'm using that template to track a person to person loan, and it works quite well. It was even already set up to adjust for payments made above the minimum.

I found it by using the list of template sources in the wiki.

Lorinda

[sardisson]

[NeoPhyte]

Thank you both so much, Jacqueline and Lorinda. We'll play with this a bit and report back. We greatly appreciate your help!

[jgd]

[jgd]

NeoPhyte,

I hope that I've a file which works. You can download it from here. Click on the little arrow at the right side of loan-amortization-schedule -2 .xls.

If there is no problem, save it as Template. If you find problems let me know

Jacqueline