London Banter - View Single Post

Tim Woodall · May 11th 09, 05:41 PM posted to uk.transport.london

On Fri, 8 May 2009 02:09:39 -0700 (PDT),
wrote:

Certainly, my first reaction was that there must have been some
expensive unresolved journeys, but it appears not. But I needed to do
some spreadsheet work to be sure.

I've also suffered missing entries, but with a correct overall
balance. Am I alone in thinking that the online display is
unnecessarily confusing? The "Fare" column doesn't actually show the
fare for a journey (except for a bus) - it shows debits and credits.
I'm interested in your spreadsheet - does anyone have anything that
you can just paste the journey history into and it actually shows the
fares charged??

(Sorry this is rather specific to unix and open office users but maybe
someone can adapt it for microsoft tools)

I use the following:

1. Save the "printer friendly version" of the journey history.

2. Run it through a script to turn the saved html in 1 into a csv file
(see end of this post for the script)

3. Import the CSV into open office.

Then (I think) adding the following columns will give you what you want:

Column I: row 94: =IF(A95+B95+2/24A94+B94;I95+E94;E94)
Column J: row 94: =IF(A95+B95+2/24A94+B94;"";I94)

Copy and paste into the other rows.

This fails in two cases: 1, if you have auto top-up that will be added
to your journey "cost" and 2, if you make two journeys the second of
which starts less than two hours after the first finished it will be
consolidated into one journey.

1 is fairly easy to fix - just create a new column that zeros out any
auto top-up entries and then change the column I formula to reference
your new column instead of column E. 2 is harder to fix - you can tweak
the 2/24 parameter - that's the number of hours for the next entry to be
added to your journey (not the total journey time)

I don't know how well this will play with capping - I've never reached a
cap - or season tickets plus extensions.

Tim.

$ cat ppv2csv
cat $1 |
sed -n '
/tr/ {
h
}
/\/tr/ {
H
x
//g s/
s/\n//g
s/\t//g
s/ *//g
s/ *//g
s/tr//g
s|/tr||g
s/td//g
s|/td|$ $|g
s/^.*$//
s/[^]*//g
s/£//g
s/- /-/g
p
}
{
H
}
' |
while read d r; do
if [[ "$d" = '$' ]]; then
d=$oldd;
else
oldd=$d
fi
echo $d $r
done |
grep -v '^$' |
sed 's/\$ \$/\t/g'

--
God said, "div D = rho, div B = 0, curl E = - @B/@t, curl H = J + @D/@t,"
and there was light.

http://www.woodall.me.uk/