Automating Chart Data generation for a Daily Expenses per Tag

Question

Using Google Spreadsheets. Here's an attempt to replicate my table needs:

2012-08-30    food     $15    expensive dinner
2012-08-30    food     $10    pizza!
2012-08-30    other    $30    that damn painting
2012-09-02    home     $40    can't remember
2012-09-02    other    $5     toilet paper
2012-09-02    home     $2     buying new flowers

I can already do 2 things with it, but it is so far from optimal it becomes negligible.

First, Using SUMIF:

food    $25    <-    SUMIF(B:B;"food";C:C)
other   $35
home    $42

Then, combining it with ARRAYFORMULA:

           food home other
2012-08-30  $25   $0   $30 <- ARRAYFORMULA(SUMIF(A:A&B:B;2012-08-30&"food";C:C))
2012-09-02   $0  $42    $5

See where this can become too big? Well...

I want to do 2 graphic charts out of this. Of course, the main one is the second:

1. A pie, from the first example. On using the SUMIF, I need to explicitly, write "food" there (or reference, whatever). Could that be done automagically filling every tag found?

   While I can live without this, it may be the answer to the second, main question;

2. A plot or timeline, from the arrayformula. It should trace each tag in a plot along the time.

Is it even possible to be done? If not, any suggestions? I'm keen to start scripting if needed (and worth it). Or move away from google. Or from spreadsheets all together (lastly). Python maybe? Ruby?

Or maybe I'll just leave it as it is, if it's tooooo much trouble.

Answer 1

In general, Spreadsheets are not Databases, and this is a task for which you are fast approaching needing a database. However, as luck would have it (depending on how you look at it, anyhow), Google Spreadsheets actually do have some database-like access APIs, so you can probably do what you want:

http://googleajaxsearchapi.blogspot.com/2008/03/introducing-latest-ajax-api-google.html

https://developers.google.com/chart/interactive/docs/querylanguage

http://blog.ouseful.info/2009/05/18/using-google-spreadsheets-as-a-databace-with-the-google-visualisation-api-query-language/

Answer 2

Using QUERY, you can generate the first table (with headers) using:

=QUERY(B:C;"select B, sum(C) where B != '' group by B label B 'Category', sum(C) 'Total'";0)

and this should be fairly easily plotted as a pie chart. You can select a range for the chart that is much longer than the current table to accommodate growing data, and the pie chart will conveniently ignore blank rows.

The second table can be generated using:

=QUERY(A:C;"select A, sum(C) where A is not null group by A pivot B";0)

and you can experiment with various chart types to achieve the desired visualisation.

---

edit

To provide a table that populates with zeros instead of blanks as per your comment; assuming the upper left (blank) cell of the table is I1, then in I2:

=SORT(UNIQUE(A:A))

and in J1:

=TRANSPOSE(SORT(UNIQUE(B:B)))

and then in J2:

=ArrayFormula(IF(I2:I*LEN(J1:1);MMULT(I2:I=TRANSPOSE(A:A);(J1:1=B:B)*C:C);IFERROR(1/0)))

Note this will populate CONTINUE functions to the far bottom and far right of the spreadsheet, over-writing everything in their path. So probably best to have a sheet dedicated to this table.