Spreadsheet Examples
Table of Contents
Plugins
I regularly make use of the %TWIKIWEB%.SpreadSheetPlugin module. The ability to define variables, and assign formulas, permits some powerful information management.
When combined with %TWIKIWEB%.EditTablePlugin I can easily update my complex tables.
This page serves to demonstrate some of the applications which I have personally made use of over the years with TWiki.
Notes
All the examples shown define variables and use the value of those variables at appropriate points. Typically a variable is set using the form:
* Set RAINDROPS = R$ROW(0):C2
The value is accessed by placing percent symbols around it, like this:
%RAINDROPS%
The Spreadsheet plugin often uses the %CALC{...}% syntax to actually perform a spreadsheet calculation on a formula. A frequent shortcut that I use is to define my calculation in a variable, e.g.:
* Set MYCALC = CALC{ ... }
I then activate that in the appropriate place by placing one set of percent symbols around the variable name to substitute that into place. I then wrap
another set of percent symbols around it to "execute" the calculation. e.g.
%%MYCALC%%
Of course one would probably want to hide their formulas from display on the page by wrapping HTML comments around the variable definitions, e.g.
<!
* Set RAINDROPS = R$ROW(0):C2
>
Examples
Car Mileage
Every time I fill up my car I want to determine how many litres per kilometre (or miles, when I was living in the UK) I've consumed for efficiency calculations. All I add into my table are four columns:
 odometer reading
 litres filled
 total price
 a comment
From that is calculated:
 kilometres per litre
 litres per 100 kilometres (an odd measure used in Australia when quoting vehicle efficiency)
 price per litre
 kilometres travelled since last fill
Variables
* define names of cells
* Set LITRES = R$ROW(0):C2
* Set COST = R$ROW(0):C3
* Set ODOTHIS = R$ROW(0):C4
* Set ODOLAST = R$ROW(1):C4
* formulas
* Set DISTANCE = $T(%ODOTHIS%)  $T(%ODOLAST%)
* calculations with formatting
* Set DIST = CALC{$FORMAT(COMMA,0,$EVAL( %DISTANCE% ))}
* Set KPL = CALC{$FORMAT(COMMA,1,$EVAL( (%DISTANCE%) / $T(%LITRES%) ))}
* Set LPC = CALC{$FORMAT(COMMA,1,$EVAL( $T(%LITRES%) / ( (%DISTANCE%) / 100 ) ))}
* Set CPL = CALC{$FORMAT(COMMA,3,$EVAL( $T(%COST%) / $T(%LITRES%) ))}
Table Definition
From that I can construct my table (warning it is complex):
%TABLE{dataalign="left,right,right,right,right,right,right,right,left" headerrows="1"}%
%EDITTABLE{format=" date,10,%DISPLAYTIME{$year$mo$day}%,%Y%m%d \
text,5, text,5, text,6, \
label,0,$percnt$percntDIST$percnt$percnt \
label,0,$percnt$percntKPL$percnt$percnt \
label,0,$percnt$percntLPC$percnt$percnt \
label,0,$percnt$percntCPL$percnt$percnt text,16,"}%
 *Date*  *Qty*  *Val*  *Odo*  *Dist*  *KPL*  *LPC*  *CPL*  *Type* 
 20080923  20.43  27.97  39811     %%CPL%%  Mobil Beecroft (E10) 
 20080930  26.45  35.95  40112  %%DIST%%  %%KPL%%  %%LPC%%  %%CPL%%  Mobil Lane Cove (E10) 
 20081007  30.37  42.50  40466  %%DIST%%  %%KPL%%  %%LPC%%  %%CPL%%  Mobil Lane Cove (E10) 
 20081014  18.87  26.00  40659  %%DIST%%  %%KPL%%  %%LPC%%  %%CPL%%  Mobil Lane Cove (E10) 
 20081021  38.29  52.42  41134  %%DIST%%  %%KPL%%  %%LPC%%  %%CPL%%  Mobil Lane Cove (E10) 
Actual Table
%EDITTABLE{format=" date,10,20200715,%Y%m%d text,5, text,5, text,6, label,0,$percnt$percntDIST$percnt$percnt label,0,$percnt$percntKPL$percnt$percnt label,0,$percnt$percntLPC$percnt$percnt label,0,$percnt$percntCPL$percnt$percnt text,16,"}%
Date 
Qty 
Val 
Odo 
Dist 
KPL 
LPC 
CPL 
Type 
20080923 
20.43 
27.97 
39811 



1.369 
Mobil Beecroft (E10) 
20080930 
26.45 
35.95 
40112 
301 
11.4 
8.8 
1.359 
Mobil Lane Cove (E10) 
20081007 
30.37 
42.50 
40466 
354 
11.7 
8.6 
1.399 
Mobil Lane Cove (E10) 
20081014 
18.87 
26.00 
40659 
193 
10.2 
9.8 
1.378 
Mobil Lane Cove (E10) 
20081021 
38.29 
52.42 
41134 
475 
12.4 
8.1 
1.369 
Mobil Lane Cove (E10) 
The columns are:
 Qty  litres
 Val  cost in AU$
 Odo  odometer reading
 Dist  kilometres travelled since last fill
 KPL  kilometres per litre travelled (efficiency since last fill)
 LPC  litres per 100 kilometres (efficiency measure in Australia)
 CPL  cost per litre, in AU$
Discussion
The following functions were used:
 $ROW(0)  this returns the current row number for the cell in which it is used
 $ROW(1)  this returns the row number for the cell above that in which it is used
 $T()  converts a row/column specifier (e.g. "R1:C2") into the text contained by the referenced cell
 $EVAL()  performs the calculation specified (e.g. "100 / 5"  returns 20)
 $FORMAT(COMMA,1,123456.789)  formats a number with commas and 1 decimal place (would return "123,456.7")
Car Loan
I had to keep track of repayments on a car loan from a family member. I was being charged interest daily and needed to calculate how much of the loan was remaining after each repayment. Determining a formula for interest was a challenge because there were no such direct formulas in the
SpreadSheetPlugin module  but a bit of high school math and the logarithm function provided what I needed.
Variables
* define names of cells
* Set THISDATE = R$ROW(0):C1
* Set LASTDATE = R$ROW(1):C1
* Set AMOUNT = R$ROW(0):C3
* Set LASTBALANCE = R$ROW(1):C6
* formulas
* Set DAYS = $INT( $TIMEDIFF( $TIME( $T(%LASTDATE%) GMT ), $TIME( $T(%THISDATE%) GMT ), day ) )
* Set DAYSD = CALC{ %DAYS% }
* Set INTERESTDAILY = $EVAL( 1 + ( 0.08 / 365 ) )
* Set INTEREST = ( $EXP( $EVAL( $LN( %INTERESTDAILY% ) * %DAYS% ) ) * \
$VALUE($T(%LASTBALANCE%)) \
)  $VALUE($T(%LASTBALANCE%))
* Set THISBALANCE = $VALUE($T(%LASTBALANCE%)) + $VALUE($T(%AMOUNT%)) + %INTEREST%
* calculations with formatting
* Set INTERESTD = CALC{ $FORMAT(COMMA,2,$EVAL(%INTEREST%)) }
* Set BALD = CALC{ $FORMAT(COMMA,2,$EVAL(%THISBALANCE%)) }
Table Definition
%TABLE{dataalign="left,left,right,right,right,right"}%
%EDITTABLE{format=" date,10,%DISPLAYTIME{"$year$mo$day"}%,%Y%m%d \
text,20,Repayment text,9,0.00 \
label,0,$percnt$percntDAYSD$percnt$percnt \
label,0,$percnt$percntINTERESTD$percnt$percnt \
label,0,<b>$percnt$percntBALD$percnt$percnt</b>"}%
 *Date*  *Description*  *Amount*  *Days*  *Interest*  *Balance* 
      <b>0.00</b> 
 20080902  Initial loan  18,000.00  0  0.00  <b>%%BALD%%</b> 
 20080925  Repayment  1,500.00  %%DAYSD%%  %%INTERESTD%%  <b>%%BALD%%</b> 
 20081010  Repayment  350.00  %%DAYSD%%  %%INTERESTD%%  <b>%%BALD%%</b> 
 20081024  Repayment  420.00  %%DAYSD%%  %%INTERESTD%%  <b>%%BALD%%</b> 
 20081106  Repayment  1,125.00  %%DAYSD%%  %%INTERESTD%%  <b>%%BALD%%</b> 
Actual Table
%EDITTABLE{format=" date,10,20200715,%Y%m%d text,20,Repayment text,9,0.00 label,0,$percnt$percntDAYSD$percnt$percnt label,0,$percnt$percntINTERESTD$percnt$percnt label,0,
$percnt$percntBALD$percnt$percnt"}%
Date 
Description 
Amount 
Days 
Interest 
Balance 





0.00 
20080902 
Initial loan 
18,000.00 
0 
0.00 
18,000.00 
20080925 
Repayment 
1,500.00 
23 
90.96 
16,590.96 
20081010 
Repayment 
350.00 
15 
54.63 
16,295.59 
20081024 
Repayment 
420.00 
14 
50.07 
15,925.66 
20081106 
Repayment 
1,125.00 
13 
45.44 
14,846.10 
Discussion
The following functions were used:
 $TIME()  takes a string and converts to an internal time representation
 note that the string "
GMT
" must be added to the end of the dates in order for comparisons to be unaffected by daylight savings changes (if you do a $TIMEDIFF()
across a daylightsavings time and a standard time using server/local time then calculations can be affected by a whole day)
 $TIMEDIFF(,,days)  computes the difference between two times in terms of whole days
 $LN()  computes a logarithm
 $EXP()  computes the exponential
 $VALUE()  takes a string and converts to number (e.g. "$12,345.67" becomes 12345.67)
Spend Tracker
I don't own my own property. I probably should, and that's what I'm working towards. In the meantime I pay rent. A lot of it. It is useful to keep track of how much money I've spent on rent.
Variables
It's worth noting that the
$VALUE
function gets used here, and the reason is that commas in numbers tend to throw calculations off  the
$VALUE
function turns numbers with commas into something that can be used in calculations.
* define names of cells
* Set CELL_LASTTOTAL = R$ROW(1):C4
* Set CELL_THISAMT = R$ROW(0):C2
* formulas
* Set AMT_LASTTOTAL = $VALUE($T(%CELL_LASTTOTAL%))
* Set AMT_THISAMT = $VALUE($T(%CELL_THISAMT%))
* Set AMT_THISTOTAL = $EVAL( %AMT_LASTTOTAL% + %AMT_THISAMT% )
* calculations with formatting
* Set DISP_TT = CALC{ $FORMAT(COMMA,2,%AMT_THISTOTAL%) }
Table Definition
%TABLE{dataalign="left,right,left,right"}%
%EDITTABLE{format=" date,10,%DISPLAYTIME{"$year$mo$day"}%,%Y%m%d \
text,6,0.00 text,20, \
label,0,<b>$percnt$percntDISP_TT$percnt$percnt</b>"}%
 *Date*  *Amount*  *Purpose*  *Total* 
 20081003  260.00  Rent for 20081025 to 20081031  <b>%%DISP_TT%%</b> 
 20081027  1,040.00  Tenancy deposit of 4 week's rent  <b>%%DISP_TT%%</b> 
 20081027  260.00  Rent for 20081101 to 20081107  <b>%%DISP_TT%%</b> 
 20081027  15.00  Administration fee  <b>%%DISP_TT%%</b> 
 20081106  520.00  Rent for 20081108 to 20081121  <b>%%DISP_TT%%</b> 
 20081120  520.00  Rent for 20081122 to 20081205  <b>%%DISP_TT%%</b> 
 20081204  520.00  Rent for 20081206 to 20081219  <b>%%DISP_TT%%</b> 
Actual Table
%EDITTABLE{format=" date,10,20200715,%Y%m%d text,6,0.00 text,20, label,0,
$percnt$percntDISP_TT$percnt$percnt"}%
Date 
Amount 
Purpose 
Total 
20081003 
260.00 
Rent for 20081025 to 20081031 
260.00 
20081027 
1,040.00 
Tenancy deposit of 4 week's rent 
1,300.00 
20081027 
260.00 
Rent for 20081101 to 20081107 
1,560.00 
20081027 
15.00 
Administration fee 
1,575.00 
20081106 
520.00 
Rent for 20081108 to 20081121 
2,095.00 
20081120 
520.00 
Rent for 20081122 to 20081205 
2,615.00 
20081204 
520.00 
Rent for 20081206 to 20081219 
3,135.00 
Timesheet
As a contractor I like to keep track of exactly how many days I've worked, how many days were public (or "bank") holidays, and how many days I had off due to sickness or intentional leave.
Because I want a summary at the bottom of the table I cannot use the
EditTablePlugin module  which is fine, I can edit the table manually in text mode  what is important are the formulas that make my life easier.
Variables
* calculates total of column above, adding commas and decimal figure
* Set MS = CALC{$FORMAT(COMMA,1,$SUM( $ABOVE() ))}
* calculates sum of daily values on current row, adding commas and decimal figure
* Set MD = CALC{$FORMAT(COMMA,1,$SUM( R$ROW(0):C2 .. R$ROW(0):C6 ))}
Table Definition
 *Week*  *Day*  *Days*  *Pay*  *Misc* 
 *Starting* *Mon* *Tue* *Wed* *Thu* *Fri* *Worked* *Pub* *Sck* *Lea* *Date*  *Note* 
 20071126 1.0 1.0 1.0 1.0 1.0 %%MD%% 0.0 0.0 0.0 20080102 
 20071203 1.0 1.0 1.0 1.0 1.0 %%MD%% 0.0 0.0 0.0 20080109 
 20071210 1.0 1.0 1.0 1.0 1.0 %%MD%% 0.0 0.0 0.0 20080116 
 20071217 1.0 1.0 1.0 1.0 0.5 %%MD%% 0.0 0.0 0.5 20080123 Half day leave to Paris 
 20071224 1.0 0.0 0.0 1.0 1.0 %%MD%% 2.0 0.0 0.0 20080130 Christmas and Boxing Day 
 *Weeks: %CALC{"$EVAL( $LISTSIZE( $ABOVE() )  2 )"}%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
*%%MS%%* \
** \
** 
Actual Table
Weeks: 5 
5.0 
4.0 
4.0 
5.0 
4.5 
22.5 
2.0 
0.0 
0.5 


Week 
Day 
Days 
Pay 
Misc 
Starting 
Mon 
Tue 
Wed 
Thu 
Fri 
Worked 
Pub 
Sck 
Lea 
Date 
Note 
20071126 
1.0 
1.0 
1.0 
1.0 
1.0 
5.0 
0.0 
0.0 
0.0 
20080102 

20071203 
1.0 
1.0 
1.0 
1.0 
1.0 
5.0 
0.0 
0.0 
0.0 
20080109 

20071210 
1.0 
1.0 
1.0 
1.0 
1.0 
5.0 
0.0 
0.0 
0.0 
20080116 

20071217 
1.0 
1.0 
1.0 
1.0 
0.5 
4.5 
0.0 
0.0 
0.5 
20080123 
Half day leave to Paris 
20071224 
1.0 
0.0 
0.0 
1.0 
1.0 
3.0 
2.0 
0.0 
0.0 
20080130 
Christmas and Boxing Day 

PeterPayne  12 Feb 2009
Great contribution. Could you make this ready for documentation? Then we can add this to the svn repository.

ArthurClemens  12 Feb 2009
By all means take this and make it your own. I've presented this, here, so that others, who may want to flex their spreadsheet muscles, can see how it's done without having to spend many days and weeks figuring it out for themselves.

PeterPayne  26 Feb 2009
I have created
Tasks.Item8693 for this.

ArthurClemens  10 Mar 2010