In this example I will demonstrate some of the string and web litLIB functions and build a twitter spreadsheet in which you can view the tweets from some famous people.

I will demonstrate how a web page can be called with parameters and how the returned HTML can be searched for useful information. Please see the end of this topic for a print of the prepared (and attached) spreadsheet.

In this topic I will demonstrate how to get the tweets from these people into a spreadsheet. This technique can be applied to all kinds of different situations including the reading of RSS feeds, the use of web services...

Step 1: Retrieving the twitter webpage: The simpel formula to retrieve the twitter page is as follows: =WebPage("twitter.com/jamie_oliver")

To make it interactive, we want to choose from a list of people, we change it such that the name of the twitter account is read from a cell: =WebPage("twitter.com/" & F3)

I have setup a list in the same spreadsheet at T3:T7 with the names of the twitter accounts I am interested in. I have created in F7 a dropdown list using the data validation feature of Excel. Select Data - Validation and select: Allow=List, Source: =$T$3:$T$8 When you select cell F7 a dropdown list appears with all the names stored in the list. When you select a name, Excel will cause the WebPage function to be recalculated and litLIB fetches the page for the seleted twitter account.

Step 2: Extracting useful information from the web page: So far the twitter page is displayed only in source in one cell. We want to see the individual tweets. Inspecting the HTML it appears that all tweets start with: <span class="entry-content">. So the first task is to retrieve all instances of this text. One per row. I started in B5 with the formula: =InStr(1,F$26,"<span class=""entry-content"">") The InStr function is used to find the substring in the HTML string starting at position 1. In F26 the WebPage displays the page HTML. Note that I have doubled the double quotes. The next row needs to continue the search from where the first tweet was found: =InStr(B5+1,F$26,"<span class=""entry-content"">")

To extract the tweet we need to know where the tweet information ends. After inspecting the HTML we see that it ends with </span>. So a search needs to be done to find these strings. In C5 I coded: =InStr(B5,F$26,"</span>") and in C6: =InStr(B6,F$26,"</span>"), etc.

The spreadsheet now displays the positions where these strings could be found. Now the task is to extract the characters in between these two positions. In column E I will collect them, coding in E5: =MID(F$26,B5+28,C5-B5-28) and similarly in E6: =MID(F$26,B6+28,C6-B6-28), etc. I have used the Excel function MID to extract a substring starting at position B5 and 26 characters further since this is how long our search string was.

Extracting the tweet text concludes our example. In a similar way the tweet's timestamp can be extracted. The helper columns with the text positions can be hidden using Format - Row/Columns - Hide

Copyright (C) 1995-2007 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.
sitemap