[wdmmg-discuss] Budgetiser...

Fri Jul 30 16:23:22 UTC 2010

When the Guardian first opened up their datastore using google docs, i started
hacking around a datastore explorer:

http://ouseful.open.ac.uk/datastore/gspreadsheetdb4.php?run=true&gsKey=0AjRWhOOrlkGIdEZnZ000Tk5qYTlyU3pVZF9xUVR2OXc&gid=1&gqc=A,%20B,C,E,I&gqw=E%20%3E0&gqo

(scroll down) 

Idea was you could use it to compile simple database like queries over
spreadsheet contents....

(There is a big issue that you really need to have a good idea of the structure
of the spreadsheet to effectively query it)

This idea of using a spreadsheet as a database and providing a query and
charting UI over the top is one that is developed in a slightly (though
imho,not very, more polished way by google fusion tables)

Anyway, we can do other queries eg
forecast with deficit greater than a specified amount
http://ouseful.open.ac.uk/datastore/gspreadsheetdb4.php?run=true&gsKey=0AjRWhOOrlkGIdEZnZ000Tk5qYTlyU3pVZF9xUVR2OXc&gid=1&gqc=A%2CB%2CK%2CN&gqw=%20B%20contains%20%27Forcast%27%20AND%20K%3E1200&gqo

Receipts more than one and expenditure less than another
http://ouseful.open.ac.uk/datastore/gspreadsheetdb4.php?run=true&gsKey=0AjRWhOOrlkGIdEZnZ000Tk5qYTlyU3pVZF9xUVR2OXc&gid=1&gqc=A%2CB%2CH%2CI&gqw=%20H%3E10%20AND%20I%3C700&gqo

Results where expenditure minus receipts is greater than 40:
http://ouseful.open.ac.uk/datastore/gspreadsheetdb4.php?run=true&gsKey=0AjRWhOOrlkGIdEZnZ000Tk5qYTlyU3pVZF9xUVR2OXc&gid=3&gqc=A%2CB%2CH%2CI&gqw=%28I-H%29%3E40&gqo

At the moment, each query is to a particular sheet; what google fusion tables
would allow is the merging of data from sheets (you'd have to set column names
appropriately - each prefixing them with current sheet name) so that then you
could run a single query across everything. eg years in which pre and post
deficits differed by more than a certain amount.

I started to consider what a single spreadsheet version may look like too eg
for the COI website spend data:
http://ouseful.open.ac.uk/datastore/coiwebsites.php?run=true&gqc=A%2CB%2CQ%2CAL%2CAZ&gqw=AL%20%3E%20%27%27&gqo

Maybe it would be more useful for me to think about how i could compare data
from different sheets, maybe using the same queries applied to each sheet
(assuming data is organised same way in different sheets)?

tony

PS code is horrible... and contains redundant functions, failed cribs, etc etc
;-(

-----------------------------------------------------------
Tony Hirst
mailto:a.j.hirst at open.ac.uk
blog: http://ouseful.info
twitter: @psychemedia

Dept. of Communication & Systems,
The Open University, Walton Hall,
Milton Keynes, MK7 6AA, UK

Tel: +44 (0)19086 52789, m./SMS 07771 330 372

----------------------------------------------------------
The Open University is incorporated by Royal Charter (RC 000391), an exempt
charity in England & Wales and a charity registered in Scotland (SC 038302)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/openspending/attachments/20100730/80f1ddde/attachment.html>