Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litLIB: Twitter spreadsheet example (Read 1677 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB: Twitter spreadsheet example
22.11.09 at 22:31:51
 
litLIB: Twitter spreadsheet example
 
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.
 
Twitter, what is it?  (www.twitter.com)
Twitter is a system where people let others know what they are doing. Useful? No? Would you like to know what Barack Obama is doing? or Jami Oliver? or .... LITSoftware (that's us!) It is amazing how many people twitter these days. To see what famous people are twittering try the following twitter pages:
Barack Obama: http://twitter.com/BarackObama
Jami Oliver: http://twitter.com/jamie_oliver
Al Gore: http://twitter.com/algore
 
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...
 
To start we need litLIB (www.oraxcel.com/project/litlib) the Excel power functions pack. You can download it from here: http://www.oraxcel.com/downloads/index.htm?product=litlib
 
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
 
Please find attached the spreadsheet I prepared.  
 
And enjoy!
Back to top
« Last Edit: 19.12.09 at 10:23:54 by Gerrit-Jan Linker »  

twitter.gif

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1