• Easily means : minimum programming skills. In our case you just need to slightly modify some spreadsheet value or formula.
• Dynamically means that the data will update itself, you don't have to do anything, any change on Kiva.org is reflected on the web page.

1. Find the source of the information, in our case Kiva statistics. Kiva allows access to these in several format, JSON, XML, RSS, HTML, etc. In our case we'll use the XML format.
   
   
2. Create a spreadsheet online using Google Documents. There are several functions (formulas) that allow you to import selected data from an external source and fill a spreadsheet table automatically.
   
   
3. Visualization: several types of charts are available from google, like bar graphs, lines, pie graph, maps, etc. The data to be represented come from a spreadsheet.

Using your team id

A little bit of XML

From XML to Google Documents

What you want now is to take the XML description of your team and insert it in a spreadsheet. The ImportXML() formula just does that.

Select the cell in A2 type in this formula :

=ImportXML("http://api.kivaws.org/v1/teams/20633.xml", "/response/teams/team")

The second line should be automagically populated by your team info!

You can (manually) populate the first row with the XML id element names:

About updates

Most of the charts are dynamic, when the data on the underlying spreadsheet changes, the charts is updated. You can prevent this by using a snapshot  which will create a chart as a static image. The later is obviously faster and recommended for static data.

=ImportXML()

The first parameter to this function is an URL, which should return XML data.

The second is an XPATH, e.g. a path selection to find the data into the XML tree.

Instead of =ImportXML("http://api.kivaws.org/v1/teams/20633.xml", "/response/teams/team") you can try :

=ImportXML("http://api.kivaws.org/v1/teams/20633.xml", "//team")

"//team" means "search from the root element the element named "team" and return it. Since there is only one team element in that tree both results are the same.

Visualizing the data

To display the 3 last columns (member_count, loan_count, loaned_amount) choose Chart from the Insert menu and enter Sheet1!L1:N2 and select "Recommended charts" a vertical bar graph.

Inserting the chart on a web page

If the chart is to be inserted on a Google Site all you need to do is select Chart in the Insert format, the list of all your spreadsheets files will be displayed, select the one you want to get the data from, and the same form as above is displayed. Yes, it's that simple.

If you want to insert the chart in a hosted web page, click on the top left of the chart itself, there is a short menu with an "Publish chart..." item.

 which will show a javascript code that you should cute and paste into your web page:

 

Ïnteractive chart means that the chart will display more information when you move the mouse over the bars (or different areas of the chart).

Note that the spreadsheet must be saved before you can export a chart.

Demo spreadsheet view

From whch you can cut & paste values. Note the Sheet1..3 tab at the bottom.

Demo spreadsheet

Demo spreadsheet

Thank me ;-)

Did you like this little tutorial ?

You can either:

1. Become a member of my team: http://kiva.org/invitedto/kathleen/by/stephanefr
2. As above but you make a loan on behalf of the team
3. Make a little donation which we'll be used on Kiva.

If you do any of the above you can have your website hosted under freedonne.org and/or be listed on http://sponsors.freedonne.org.

The gadget spec URL could not be found

Advanced hacks

Since we have populated the line 1 with the elements name, and this name is actually the last part of the XPATH, we can use the first line values in the formula. Don't panic, it's easy. The content of A2 is /response/teams/team/id and id is the value of A1, same for the second column, A2 is /response/teams/team/shortname and "shortname" it's what's written in B1.

So each value displayed on the second line is the one of the element /response/teams/team/<value of line 1 in the sheet>.

If you want to get the member_count in a cell, use the following formula:

=ImportXML("http://api.kivaws.org/v1/teams/20633.xml", "/response/teams/team/"&L1)

The operator "&" concatenates 2 strings, since L1 contains member_count the second parameter becomes /response/teams/team/member_count.

Getting the list of loans

Syntax: http://api.kivaws.org/v1/teams/<team id>/loans.xml

Example: http://api.kivaws.org/v1/teams/20633/loans.xml

A graphic representation is on Sheet2 of our demo document.

Import data from the Category page

Each team category has its own page, here are some:

http://www.kiva.org/community?category=Religious+Congregations

http://www.kiva.org/community?category=Memorials

http://www.kiva.org/community?category=Clubs

On the right side you see 2 tables under "TEAM LEADERBOARDS". If you inspect the HTML code, you'll find that each name is a list item, e.g. between <li></li> tags:

The list content is highligthed below:

There is a function on Google documents Spreadsheet that allows to import any table (eg. content between <table> and </table>) or list (<li>...</li>):

ImportHTML(URL, type, index) where:

URL is the page address (eg. "http://www.kiva.org/community?category=Memorials") type is either "table" or "list" and index tells which list or table of the page, they can be many, to import into the spreadsheet.

If you enter "=importHtml("http://www.kiva.org/community?category="&$D$2, "list", 11)" in A2 (and D2 contains the category name, "Memorial" in this example), here is the output:

This data can already be displayed in a table chart like above but with a better formatting.

Now we want to output the position of a given team, which exact name is stored in D4 during the 3 periods "All Time", "Last Month". "This Month".

What we did is duplicate the table above a little bit further (columns E - H) and removed the numbers at the end of each cell. This is done by RegexpReplace(text, regexp, replacement), which replace what is matched by regexp in text by the new text "replacement". Regexp is short for Regular Expression and it's a way of specifying a text pattern. In our case we want to remove the digits at the end of the team name. The regexp which will do that is "[0-9]*$" : [0-9] is a range definition matching any digit, * means "0 or more" and $ is end of line, in english it can be translated by "all the trailling digits". It seems there are spaces before the first digit, so we have added " *" : " *[0-9]*$".

Since we want to delete the digits, the last parameter of RegexpReplace will be the null string "".

Because we're lazy, the formula "=REGEXREPLACE(A:A," *[0-9]*$","")" has been used in every cell of F:H.

The column E just numbers each line (=row()).

To find the rank of your team, you just need to select the column E after keeping only the lines where the team name matches.

This is done on Sheet2 with the formula : =filter('Sheet1'!E:H, 'Sheet1'!F:F='Sheet1'!$D$4)

which means "take the array E:H from Sheet1 and keep only the lines where the value of column F = the team name".

The next line will do the same on column G then H.

The header of the first table on Sheet1 ( "All Time", "Last Month". "This Month" <) is a hack: it's the result of the formula =transpose(importHtml("http://www.kiva.org/community?category="&$D$2, "list", 10)) which takes the value of the 10th list defined on the http://www.kiva.org/community?category=<some category> page but transposed so it's made horizontal rather than vertical.

To display the data we just take the first 2 columns of sheet2.

Below, our spreadsheet.

Team position in category

And the chart

Personal stats

I've been ask if it was possible to use Google tools to display personal Kiva statistics. Yes it is ;-)

Please look at this spreadsheet.

NOTES

1. All the requests to Kiva servers should end with app_id="your domain name" (in reverse DNS notation, eg. foo.com becomes com.foo).
2. On the above example we don't do a good job at paging, eg. Kiva servers will return only X (usually 20) results at a time, you have to do multiple queries to pull all the data you need. The paging parameters are returned at the beginning of the response:

<paging>

<page>1</page>

<total>272</total>

<page_size>20</page_size>

<pages>14</pages>

</paging>

Pages are accessed by adding the page= parameter on the query.

BUGS

Sometimes the chart doesn't update, but only after refresh.

The charts range must be known in advance, which is not always possible to know. For example the number of countries covered by Kiva will change over time, so the # of rows will change but the chart gadget needs a fixed range, e.g. Sheet1:A1:B28.