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/

Thanks muchly for your efforts on this, but I don't think it's going
to help as it's mostly gobbledygook to me!!! I just wish the online
journey history wasn't so complicated to make sense of (let alone
reflect the journey history accurately, which was the origin of this
thread!).
Regards
Tim

On 11 May, 18:41, Tim Woodall wrote:
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/

On Thu, 14 May 2009 07:32:00 -0700 (PDT),
Tim wrote:
Thanks muchly for your efforts on this, but I don't think it's going
to help as it's mostly gobbledygook to me!!! I just wish the online
journey history wasn't so complicated to make sense of (let alone
reflect the journey history accurately, which was the origin of this
thread!).
Regards

I've found it's very much easier than that :-)

You can import the "printer ready" version of the journey history
straight into openoffice.

I've build a spreadsheet where all you need to do is:
1. Save the printer ready journey history to a file
2. Insert-Sheet From File and select the file created in 1
3. Change Cell B1 on the "Journeys" sheet to the name of the sheet you
created in 2.

That's it. Enjoy your journey history.

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

http://www.openoffice.org/

(Note that I'm using version 2.4 of openoffice. I assume later versions
will open this file without any problems)

I've included two sheets of my journey history just so you can see how
it works, including what happens when there are missing items in the
journey history.

The sheet "Workings" is used for internal calculations, just ignore it
unless you want to enhance the way it calculates the start and end of
journeys. At the moment the logic is that if a row happens more than two
hours after the previous row then it's a journey start, otherwise it's
a journey continuation.

(For anyone who wants to improve this, the two critical columns are H
and I on this page. As long as these are right, everything else should
automatically be correct - bugs excepted)

Openoffice does have the option to save as an excel file but as I have
not way to test this I've not done it.

Note also that I have a very boring life on oyster. I've got no capped
journeys, no cash top-ups etc. These may not work properly. If anyone
wants to provide printer ready journey histories with these in I'll try
and enhance the spreadsheet to handle them properly

Tim.

p.s. I've protected the two critical sheets (other than cell B1 on
Journeys.) There's no password set to this can just be turned off if you
want to change things.

--
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/