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
Using litLIB to retrieve financial information (Read 1527 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Using litLIB to retrieve financial information
04.12.09 at 09:25:38
 
Using litLIB to retrieve financial information
 
In this topic I will describe how litLIB can be used to retrieve financial information from Yahoo.
You can download litLIB from here: http://www.oraxcel.com/downloads/index.htm?product=litlib
 
It is really simpel actually. All you need is a little knowledge of HTML and a basic knowledge of Excel formulas.
 
Step 1:
Find the web page that displays the information you want to capture in your spreadsheet. Just use your webbrowser to find the page you want to use. As an example I have looked up the FTSE100 index value at Yahoo Finance:
http://finance.yahoo.com/q?s=^FTSE
 
Step 2:
Find out what to extract:
In the middle of the page is displays: Index Value:      5,313.00
I would like to capture the value of 5,313.
 
Step 3:
Retrieve the webpage in Excel. Type in A1 the following formula (you need to have litLIB installed to use this. (Download litLIB here):
A1: =WebPage("http://finance.yahoo.com/q?s=^FTSE")
 
As you can see I just copied the url from the browser into the formula.
 
Step 4:
Look in the retrieved values for the string to capture. I will look for "Index Value:" because I know that the value of interest follows it. The value will vary of course depending on the market.
 
In A2 I type a formula that will display the position where "Index Value:" is found:
A2: =Instr(1,A1,"Index Value:")
It searches the string in A1, that is our HTML from Yahoo, for the search string.
It displays the value 8454
 
Since it is handy to look at the portion of the HTML around this position I modify formula 1 to retrieve 100 characters from the HTML from position 8450:
A1: =WebPage("http://finance.yahoo.com/q?s=^FTSE",8450,100)
Result:
8%">Index Value:</th><td class="yfnc_tabledata1"><big><b><span id="yfs_l10_^ftse">5,291.52</span></b
 
Step 5:
What remains is to copy the value. I have used litLIB's TextBetween function for this. Note that I doubled the double quote in the search string.
 
A3: =TextBetween(A1,"ftse"">","</span>")
Result: 5,291.52
 
But.... we wanted to capture the value 5,313.00. Why is that?
The market actually went down (I should have chosen a better day, sorry) and the market value is at the time I ran the formula 5,291.52
 
Just imagine what you can do with these formulas yourself. Use it to retrieve stock information, weather data, scan RSS feeds for interesting things, etc, etc.
 
litLIB download:
http://www.oraxcel.com/downloads/index.htm?product=litlib
Back to top
 
« Last Edit: 04.12.09 at 09:34:51 by Gerrit-Jan Linker »  

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