You are here: Foswiki>Main Web>WikiUsers>PeterPayne>PPSpreadsheetExamples (07 Feb 2021, PeterPayne)Edit Attach

Spreadsheet Examples

Table of Contents

Plug-ins

I regularly make use of the SpreadSheetPlugin module. The ability to define variables, and assign formulas, permits some powerful information management.

When combined with 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 (subsequently Foswiki).

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

megaphone 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* |
| 2008-09-23 | 20.43 | 27.97 | 39811 |  |  |  | %%CPL%% | Mobil Beecroft (E10) |
| 2008-09-30 | 26.45 | 35.95 | 40112 | %%DIST%% | %%KPL%% | %%LPC%% | %%CPL%% | Mobil Lane Cove (E10) |
| 2008-10-07 | 30.37 | 42.50 | 40466 | %%DIST%% | %%KPL%% | %%LPC%% | %%CPL%% | Mobil Lane Cove (E10) |
| 2008-10-14 | 18.87 | 26.00 | 40659 | %%DIST%% | %%KPL%% | %%LPC%% | %%CPL%% | Mobil Lane Cove (E10) |
| 2008-10-21 | 38.29 | 52.42 | 41134 | %%DIST%% | %%KPL%% | %%LPC%% | %%CPL%% | Mobil Lane Cove (E10) |

Actual Table

%EDITTABLE{format="| date,10,2024-04-25,%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
2008-09-23 20.43 27.97 39811       1.369 Mobil Beecroft (E10)
2008-09-30 26.45 35.95 40112 301 11.4 8.8 1.359 Mobil Lane Cove (E10)
2008-10-07 30.37 42.50 40466 354 11.7 8.6 1.399 Mobil Lane Cove (E10)
2008-10-14 18.87 26.00 40659 193 10.2 9.8 1.378 Mobil Lane Cove (E10)
2008-10-21 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

megaphone 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> |
| 2008-09-02 | Initial loan | 18,000.00 | 0 | 0.00 | <b>%%BALD%%</b> |
| 2008-09-25 | Repayment | -1,500.00 | %%DAYSD%% | %%INTERESTD%% | <b>%%BALD%%</b> |
| 2008-10-10 | Repayment | -350.00 | %%DAYSD%% | %%INTERESTD%% | <b>%%BALD%%</b> |
| 2008-10-24 | Repayment | -420.00 | %%DAYSD%% | %%INTERESTD%% | <b>%%BALD%%</b> |
| 2008-11-06 | Repayment | -1,125.00 | %%DAYSD%% | %%INTERESTD%% | <b>%%BALD%%</b> |

Actual Table

%EDITTABLE{format="| date,10,2024-04-25,%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
2008-09-02 Initial loan 18,000.00 0 0.00 18,000.00
2008-09-25 Repayment -1,500.00 23 90.96 16,590.96
2008-10-10 Repayment -350.00 15 54.63 16,295.59
2008-10-24 Repayment -420.00 14 50.07 15,925.66
2008-11-06 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 daylight-savings 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

megaphone 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* |
| 2008-10-03 | 260.00 | Rent for 2008-10-25 to 2008-10-31 | <b>%%DISP_TT%%</b> |
| 2008-10-27 | 1,040.00 | Tenancy deposit of 4 week's rent | <b>%%DISP_TT%%</b> |
| 2008-10-27 | 260.00 | Rent for 2008-11-01 to 2008-11-07 | <b>%%DISP_TT%%</b> |
| 2008-10-27 | 15.00 | Administration fee | <b>%%DISP_TT%%</b> |
| 2008-11-06 | 520.00 | Rent for 2008-11-08 to 2008-11-21 | <b>%%DISP_TT%%</b> |
| 2008-11-20 | 520.00 | Rent for 2008-11-22 to 2008-12-05 | <b>%%DISP_TT%%</b> |
| 2008-12-04 | 520.00 | Rent for 2008-12-06 to 2008-12-19 | <b>%%DISP_TT%%</b> |

Actual Table

%EDITTABLE{format="| date,10,2024-04-25,%Y-%m-%d| text,6,0.00| text,20,| label,0,$percnt$percntDISP_TT$percnt$percnt|"}%
Date Amount Purpose Total
2008-10-03 260.00 Rent for 2008-10-25 to 2008-10-31 260.00
2008-10-27 1,040.00 Tenancy deposit of 4 week's rent 1,300.00
2008-10-27 260.00 Rent for 2008-11-01 to 2008-11-07 1,560.00
2008-10-27 15.00 Administration fee 1,575.00
2008-11-06 520.00 Rent for 2008-11-08 to 2008-11-21 2,095.00
2008-11-20 520.00 Rent for 2008-11-22 to 2008-12-05 2,615.00
2008-12-04 520.00 Rent for 2008-12-06 to 2008-12-19 3,135.00

Timesheet

megaphone 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* |
| 2007-11-26|   1.0|   1.0|   1.0|   1.0|   1.0|   %%MD%%|   0.0|   0.0|   0.0| 2008-01-02| |
| 2007-12-03|   1.0|   1.0|   1.0|   1.0|   1.0|   %%MD%%|   0.0|   0.0|   0.0| 2008-01-09| |
| 2007-12-10|   1.0|   1.0|   1.0|   1.0|   1.0|   %%MD%%|   0.0|   0.0|   0.0| 2008-01-16| |
| 2007-12-17|   1.0|   1.0|   1.0|   1.0|   0.5|   %%MD%%|   0.0|   0.0|   0.5| 2008-01-23| Half day leave to Paris |
| 2007-12-24|   1.0|   0.0|   0.0|   1.0|   1.0|   %%MD%%|   2.0|   0.0|   0.0| 2008-01-30| 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
2007-11-26 1.0 1.0 1.0 1.0 1.0 5.0 0.0 0.0 0.0 2008-01-02  
2007-12-03 1.0 1.0 1.0 1.0 1.0 5.0 0.0 0.0 0.0 2008-01-09  
2007-12-10 1.0 1.0 1.0 1.0 1.0 5.0 0.0 0.0 0.0 2008-01-16  
2007-12-17 1.0 1.0 1.0 1.0 0.5 4.5 0.0 0.0 0.5 2008-01-23 Half day leave to Paris
2007-12-24 1.0 0.0 0.0 1.0 1.0 3.0 2.0 0.0 0.0 2008-01-30 Christmas and Boxing Day

Comments

-- 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
Edit  | Attach | Print version | History: r12 < r11 < r10 < r9 | Backlinks | View wiki text | Edit wiki text | More topic actions
Topic revision: r12 - 07 Feb 2021, PeterPayne
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy