Convert Calc workbook to Base

[SilkBC]

I have a OO.org Calc workbook that I use to calculate ratings on soccer teams. The same setup can be used to also calculate ELO-type ratings for tennis players. It has done a great job to now, because I want to expand the amount of data it calculates for (i.e., the number teams to calculate for). The problem is, because almost every cell contains a formula, this greatly increases the size of the workbook (it went from several hundred KB for a 20-team league to well over 45MB when I tried to do all international matches), and it would take forever when navigating around it. I feel it is time to move the data into Base.

Most of the data I use are from CSV files located at <http://www.football-data.co.uk>.

Obviously more details are needed, but I will provide to anyone who is interested in working on this. I think the best way to go would be to provide the workbook I currently use, and it should be easier to see what I am after.

Thanks for your interest!

-SilkBC

[Zizi64]

see:
http://user.services.openoffice.org/en/ ... 8&p=150599

[SilkBC]

see:
http://user.services.openoffice.org/en/ ... 8&p=150599

Thanks for the link, but I am not looking to import a Calc spreadsheet into Base. I have a Calc worksheet that calculates ELO-type ratings for teams based on their results against other teams. It works fine for a 20-team league over a season, but I want to expand it quite a bit (data for several seasons readily available, doing all international matches, etc.), but because almost every cell has a formula, it greatly increases the size of the workbook.

I am looking to convert the *functionality* of the Calc workbook into a Base "application". I think the trickiest part (for me), would be writing the BASIC code required to do the calculations and provide the reports.

-SilkBC

[eremmel]

Depending on the issue you have with your spreadsheet(s) there might be a solution:
If you have trouble with CPU/calculations, it might be difficult to solve.
If you have trouble with size of document there might be a solution (I tested with a file of 10MB containing individual functions per cel" C1is "=A1+B1", extended over C1:AA65536):
- use array formulas (check the help of OO "Array Functions"; saved me 89% on file size and 50% on loading time)
- use Names to make relative 'functions' and address them with the defined name. (Saved me 85% on file size, but not on loading data)

[SilkBC]

Hi eremmel.

Depending on the issue you have with your spreadsheet(s) there might be a solution:
If you have trouble with CPU/calculations, it might be difficult to solve.
If you have trouble with size of document there might be a solution (I tested with a file of 10MB containing individual functions per cel" C1is "=A1+B1", extended over C1:AA65536):
- use array formulas (check the help of OO "Array Functions"; saved me 89% on file size and 50% on loading time)
- use Names to make relative 'functions' and address them with the defined name. (Saved me 85% on file size, but not on loading data)

I did look at these, but unfortunately they don not work for me as I have absolute references in my forumlas. Here is what the basic formula looks like:

Code: Select all

=IF($G4=V$2,IF($L4="H",V3+$U4,IF($L4="A",V3-$T4,IF($L4="D",(V3-$T4)+(($T4+$U4)/2),V3))),IF($H4=V$2,IF($L4="H",V3-$U4,IF($L4="A",V3+$T4,IF($L4="D",(V3-$U4)+(($T4+$U4)/2),V3))),V3))

-SilkBC

[eremmel]

The above kind of functions can be written in pure SQL. You do not need any macro's for that.

[SilkBC]

The above kind of functions can be written in pure SQL. You do not need any macro's for that.

Which brings me back to Square One: I would like to get a quote for converting my "Calc" workbook to an "Base" application....

Since no one else seems interested, I may have to go elsewhere, but I figured this would be the best place to start.

I appreciate your earlier thoughts on this, though

-SilkBC