How to unpick bills for GCP¶

This guide shows you how to respond to take the invoices sent by TI Sparkle (in PDF format) and process them to be able to check that they have got the bills correct. Unfortunately the bills often don't align making this process necessary.

Convert the PDF into a plain text document¶

Our GCP bills arrive in two documents. The images below have been edited to remove any confidential information. The short version one is less useful, and just presents a number

Example short invoice — An example of the short version of the invoice.

The other, much more useful version from the second page onwards is a long table of costs

It is this latter one which provides useful information for checking.

Open this second PDF and cut and paste from the "project.name cost Usage Discounts OCRE Discount Final Cost" row all the way down to the final row in the table.

and feed it into a bash shell where you have run something like:

cat > /tmp/GCP-November

The filename is whatever makes sense.

Convert plain text into a CSV¶

Then CnP the following shell function into your shell and run it

makecsv() {
if [ -z "$1" ]
then
        echo "Create the relevant file from the Cost report by CnPing the billing data into a file and then use"
        echo "$0 <filename>"
else
        (echo \"Project Name\" , Cost , Usage Discounts , OCRE Discount , Final Cost ;
         cat "$1" | grep -v '#Gruppo TIM' | sed 's/^019079-F1E96F-31B089 20[0-9][0-9][0-9][0-9] //' | sed 's/\([\.0-9]*,[0-9][0-9] £\)/, \1/' | sed 's/\([0-9]*\)\.\([0-9]*\)/ \1\2/' | sed 's/\([0-9]*\)\.\([0-9]*\)/\1\2/g' |sed 's/\([0-9]*\),\([0-9][0-9]\) £/£ \1\.\2 QQQ/g' |  sed 's/QQQ/,/g' | sed 's/,$//' |sed 's/£//g') > "$1.csv"

fi
}

$ makecsv /tmp/GCP-November
<!-- markdownlint-enable line-length -->

# This will create a file of the same name, but with .csv appended.  eg /tmp/GCP-November.csv

This will output a CSV which can be imported to your spreadsheet of choice. It has been tested on Googlesheets.

Get CSV from GCP console¶

As the admin user (CRSID@admin.g.apps.cam.ac.uk) go to https://console.cloud.google.com/billing/019079-F1E96F-31B089/reports

Group by "Scope Project"
Time Range "By invoice month" and select relevant month
Download CSV

Create spreadsheet for comparison¶

Unfortunately this is much more manual than it should be. Because names don't always appear to align.

Suggested process:

Open the two sheets in adjoining spreadsheet tabs.
Sort both sheets by names (the first column).
On the one from Sparkle hide all columns apart from the first one with the names.
Then make a new column, which takes the first matching name from sheet 2 (using = notation)

Now you can drag-expand from this entry and get the whole column from the GCP column duplicated in the Sparkle sheet.

Manually go through this column, and you will likely find that every so often the column gets misaligned as names are missing from one column or the other as a result of things being renamed or created since. Find the new location where they align, blank entries which don't align with anything, and then drag-expand from the new matching entry. Repeat till all the entries that can align do.

Now you can highlight your new column and drag-expand it to the right, and get all the rows from the GCP sheet in the same sheet as the Sparkle sheet.

The numbers won't match because they are in different currencies. However by creating a new column dividing one column by the other and get the exchange rate.

This exchange rate should be plausible, and largely consistent. The larger the numbers the more accurate the exchange rate will be.

To identify where things are out of tolerance you can sort by cost, check that the exchange rates are largely consistent across the higher cost elements (as the error bars will be smallest). Now you can create another column to compare the exchange rate calculated vs the apparent one for that line item. Start by setting a cell to be the average for line items costing over $100, something like:

=Average(D1:D29)

And then the value in each cell of the new column to something like:

=if(ABS(D2- $E$1)>0.001,"out of range","")

Where column D is the calculated exchange rate and $E$1 is the average exchange rate

Now, any exchange rates out of tolerance (here set to be 0.001) will be highlighted.

Where the exchange rate is within tolerance you can accept them. The places where the exchange rate appears to be different should be checked, and a plausible cause for the discrepancy identified. The normal reason that the values are too small, and so the exchange rate calculation has too much noise in it.

Other discrepancies previously identified include:

Marketplace spend being included, but not subject to the OCRE discounts

Name changes causing costs to appear low, because the other costs are in a different place.

Networking charges not being removed when they should be.

How to unpick bills for GCP¶

Convert the PDF into a plain text document¶

Convert plain text into a CSV¶

Get CSV from GCP console¶

Create spreadsheet for comparison¶

See also¶