Compression and large tables (Excel and CSV)

Here is an interesting little experiment: load a large CSV file into Excel. My original Powerpoint slide from a recent presentation was not as complete as it should be. Here is more size info:

Excel Never Dies

In a post by (famous economist) Paul Krugman:

I’m sometimes embarrassed at how much I use Excel. But it turns out to be a work of genius.

The online article he refers to is:

Packy McCormick, Ben Rollert, Excel Never Dies, https://www.notboring.co/p/excel-never-dies

It is a nice read.

In my work, I see Excel intensively used by (almost) all clients I work with or talk to. One sometimes wonders: what would happen if Excel would just suddenly stop working everywhere? 

Excel has a 1 million row limit: missing covid tests data

From [1]:

A million-row limit on Microsoft’s Excel spreadsheet software may have led to Public Health England misplacing nearly 16,000 Covid test results, it is understood.

The data error, which led to 15,841 positive tests being left off the official daily figures, means than 50,000 potentially infectious people may have been missed by contact tracers and not told to self-isolate. 

Many spreadsheets contain errors [2] and it is not always easy to spot them. The unstructured nature of Excel is both an advantage (flexible) but also a danger.

My usual advice is:

1. Don't use Excel as a database
2. If you need to store large data in Excel, use the data model (a.k.a. Power Query) and not the sheets. The data model is more like a real database (e.g. columns have a type, large tables ok).
3. Use more structured environments (like modeling languages) for analysis where possible and use Excel only for data entry and reporting

References

1. How Excel may have caused loss of 16,000 Covid tests in England, https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england, October 5, 2020.
2. Excel errors: How Microsoft's spreadsheet may be hazardous to your health, https://www.zdnet.com/article/excel-errors-microsofts-spreadsheet-may-be-hazardous-to-your-health/, July 2017.

Excel and statistics

I am using Excel 2007 a lot for data entry and reporting. So I was wondering about an old bug wrt the GAMMADIST function I stumbled upon when I wanted to compare my implementation of the Incomplete Gamma function with Excel. Well,

=+GAMMADIST(0.1,0.1,1,TRUE)

still produces #NUM!. This particular bug is known here.

There are other reports about accuracy issues with older versions of Excel, see e.g.:

• http://www.stat.uni-muenchen.de/~knuesel/elv/excelacc.pdf
• http://publications.npl.co.uk/npl_web/pdf/cise27.pdf
• http://www.daheiser.info/excel/frontpage.html

There have been improvements in Excel XP/2003, but some issues remained:

• http://www.stat.uni-muenchen.de/~knuesel/elv/excelxp.pdf
• http://www.forecastingprinciples.com/files/McCullough.pdf
• On the accuracy of statistical procedures in Microsoft Excel 2000 and Excel XP

In case you want to know the answer for the above formula, let’s use my implementation from GAMS:

1  scalar s;
2  s = gammareg(0.1/1,0.1);
3  option s:8;
4  display s;

----      4 PARAMETER s                    =   0.82755176 

I don’t think this is a particularly difficult point to evaluate. Nothing strange going on here. So I don’t understand why Excel has problems here.

Excel COM problem: Call was rejected by callee

I was asked to help with a problem in GAMS/GDXXRW. It threw an exception with the error "Call was rejected by callee". After some experimenting, it turned out that the culprit was a pivot table (and graph). This pivot table was using data being updated by GDXXRW and after removing the pivot table the problem went away. A command line option is added to GDXXRW to give Excel some extra time after the COM Automation Server (i.e. Excel) is started. This will allow the pivot table to initialize without interference from GDXXRW.

gdxls: write xls files on Linux

The gdxls tool is a Java based alternative for GDXXRW on Linux and other non-Windows operating systems.


Besides converting xls→gdx (as described in http://yetanothermathprogrammingconsultant.blogspot.com/2008/10/gdxls-read-xls-files-on-linux.html) we can also convert gdx→xls.

As simple example model is shown below:

$onecho > trnsport2.properties
o=trnsport2.xls
i=trnsport2.gdx
  
parameter=c
rng=a1
rdim=1
cdim=1
 
parameter.2=c
rng.2=f1
rdim.2=2
cdim.2=0
  
parameter.3=c
rng.3=sheet1!A10
rdim.3=0
cdim.3=2

$offecho
 
$call gamslib trnsport
$call gams trnsport lo=2 gdx=trnsport2
  
$call ./gdxls -propertyfile trnsport2.properties
 
$call oocalc trnsport2.xls

We run the trnsport model from the Model Library and save the results in a gdx file. We then extract the c(i,j) parameter from the gdx file and write it to the spreadsheet in different formats.

Note that we use a numbered suffix in the properties file to indicate which options belong to the same item. No suffix is identical to suffix ".1". So we have three items to process and they are numbered 1,2,3.

The result rendered by Open Office Calc looks like the picture shown below (click to enlarge).

gdxls: read xls files on Linux

The GAMS tool GDXXRW is not available under Linux, Unix and OS X.  As I needed to have this functionality in some projects, I developed a Java based alternative. Technobabble: The tool is using GCJ,GCC and CNI so it should be easy to port to any platform where the GNU compilers GCC and GCJ are available.

The tool gdxls can read xls files on Linux (and other Unix-based operating systems) to generate gdx files. Similar to GDXXRW it works as described below.

Consider the following spreadsheet (it can be an xls file from Excel or as shown from Open Office):



The GAMS model to import this file via a gdx file is:

$onecho > trnsport.properties
i=trnsport.xls
o=trnsport.gdx
 
parameter=c
rng=b4
rdim=1
cdim=1
$offecho
 
$call gdxls -propertyfile trnsport.properties
 
sets
  i 'canning plants' / seattle, san-diego /
  j 'markets'        / new-york, chicago, topeka /
;
 
parameter c(i,j);
$gdxin trnsport.gdx
$load c
 
display c;

The log will look like:

erwin@erwin-desktop:~/workspace/gdxls$ gams test1.gms
--- Job test1.gms Start 10/25/08 09:42:14
GAMS Rev 228  Copyright (C) 1987-2008 GAMS Development. All rights reserved
Licensee: GAMS Development Corporation, Washington, DC   G871201/0000CA-ANY
         Free Demo,  202-342-0180,  sales@gams.com,  www.gams.com   DC0000
--- Starting compilation
--- test1.gms(12) 2 Mb
--- call gdxls -propertyfile trnsport.properties
GDXLS V 0.1, Amsterdam Optimization (c) 2008
xls2gdx,input=trnsport.xls,output=trnsport.gdx
Parameter;name=c;range=B4:E6;rdim=1;cdim=1
xls2gdx done
--- test1.gms(20) 3 Mb
--- GDXin=/home/erwin/workspace/gdxls/trnsport.gdx
--- test1.gms(23) 3 Mb
--- Starting execution: elapsed 0:00:00.186
--- test1.gms(23) 4 Mb
*** Status: Normal completion
--- Job test1.gms Stop 10/25/08 09:42:14 elapsed 0:00:00.188
erwin@erwin-desktop:~/workspace/gdxls$

The result is:

----     23 PARAMETER c  gdxls range:B4:E6
 
            new-york     chicago      topeka
 
seattle         2.500       1.700       1.800
san-diego       2.500       1.800       1.400

Another posting will describe gdx to xls conversion.

Reading Excel XLS files on Linux

Using the code from http://poi.apache.org/ I can now read and process Excel XLS files on Linux (and Mac). 

Note: this code implements the Compound Document Format, so does not depend on Windows.

Excel PivotTable using external data

When storing a GAMS symbol each nonzero in a separate row in Excel, we quickly run out of space on older versions of Excel. Excel before Office 2007 could only handle up to 65,536 rows. To display larger pivot tables, we can write the GAMS symbol to a CSV file and use this CSV file as external data store for the Excel pivot table. This allows for larger data sets than the above mentioned row limit. See also http://www.amsterdamoptimization.com/pdf/toolhelp.pdf.

Color code an Excel spreadsheet

I received a very large spreadsheet to be converted to GAMS. To quickly get a feeling how many formulas there are and where they are located, this code colors all cells with formulas. The color depends on the type of the cell (number, string, logical or error).

Sub color()
   ActiveSheet.UsedRange.Style = "Normal"
   Call colorsub(xlNumbers, "Accent1")
   Call colorsub(xlTextValues, "Accent2")
   Call colorsub(xlErrors, "Accent3")
   Call colorsub(xlLogical, "Accent4")
End Sub
 
Sub colorsub(SpecialCells As Integer, styletype As String)
  Dim r As Range
  On Error GoTo err
 
  Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, SpecialCells)
  r.Style = styletype
 
err:
 
End Sub

An earlier version used HasFormula on each individual cell. As the spreadsheet I am working on is very large, that approach was very slow. This version returns complete ranges and is much faster.

Excel coding

Usually I do Excel programming in VBA, or Delphi and VBScript via Excel Com Automation. The Delphi tools from http://www.axolot.com/components/xlsrwii20.htm are reputed to be very fast, bypassing Excel altogether. Unfortunately it cannot read the spreadsheets I am interested in. We'll try to get the spreadsheet to them pending clients permission.