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 demo: Live stock prices in Excel (Read 7310 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB demo: Live stock prices in Excel
17.12.09 at 11:04:17
 
litLIB demo: Live stock prices in Excel
 
Using litLIB it is possible to get live stock prices in Excel. In this article I will explain how to do this just using a formula in Excel. No macros or coding required.
 
Why not try this yourself. The aex.xls spreadsheet is attached to this forum topic.
You can download litLIB from here.
 

 
Explanation:
Only the =csv function was used to make this spreadsheet. The data was retrieved from Yahoo!Finance. See the Yahoo data source below for details if you are interested.
The formula used is:
 
=csv("http://download.finance.yahoo.com/d/quotes.csv?s=" & B6 & "&f=l1c1p2&e=.csv","http")
 
The csv function takes two parameters. The first parameter is the location where to get the data. We point to a url at yahoo finance. The second parameter is http to indicate that the function should get the data from the web. I have used the string concatenation operator & to add the ticker symbols. In the example above B6 contains the symbol.
 
Note that the csv function is an array function. You need to select a range of cells and then press Ctrl+Shift+Enter to make the formula work.
 
For more information on the =csv function please see:
http://www.oraxcel.com/projects/litlib/help/csv.html
 
Yahoo data source:
http://download.finance.yahoo.com/d/quotes.csv?s=^AEX&f=sl1d1t1c1ohgvj1pp2wern&e=.csv
 
The url uses a so called ticker symbol, a short code for the stock or index you are interested in. Lookup ticker symbols at: http://finance.yahoo.com/lookup
Examples:
^AEX (AEX index Amsterdam) ^DJI (Dow Jones Industrial Average Index)  
MSFT (Microsoft) TOM2.AS (Tom Tom, Amsterdam)
For AEX index ticker symbols see: http://en.wikipedia.org/wiki/AEX_index
 
The ticker symbol of your choice should go after s= replacing ^AEX
The requested fields should go after f= replacing sl1d1t1c1ohgvj1pp2wern. For a list of fields see the definitions below.
 
Yahoo Quotes Field definitions:

  • s: the symbol name
  • l: last value / current price  
  • l1: last value / current price  
  • d1 date of last update
  • t1: the time of last update
  • c: the change amount
  • c1: the change amount
  • o: opening value
  • h: high value
  • g: low value
  • v: volume
  • j: 52-week low.
  • j1: the market cap.
  • p: after hour price (?)
  • p1:  
  • p2: change percentage  
  • w: 52-week range
  • e: EPS (Earning per share)
  • r: P/E (Prince/Earning) ratio
  • n: Company name

 
Sources:
Alex Le's Blog
http://alexle.net/archives/196
litLIB
http://www.oraxcel.com/projects/litlib
litLIB's CSV function
http://www.oraxcel.com/projects/litlib/help/csv.html
Back to top
« Last Edit: 17.12.09 at 12:23:33 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: litLIB demo: Live stock prices in Excel
Reply #1 - 17.12.09 at 14:05:42
 
Note that the formulas can be speeded up by making one single web call to retrieve the data. To do this use the SeparatedValues function to make a comma separated list of all the ticker symbols:
 
=separatedvalues(",",B6:B27)
 
Result:
agn.as,ah.as,afa.as,akza.as,amt.as,asml.as,bamnb.as,boka.as,dsm.as,fur.as,heia.a
s,ing.as,kpn.as,phia.as,rand.as,ren.as,rdsa.as,sbmo.as,tnt.as,tom2.as,una.as,wkl
.as
 
Then select a block of cells 4 columns by 21 rows and enter the formula:
 
=csv("http://download.finance.yahoo.com/d/quotes.csv?s=" & H3 & "&f=sl1c1p2&e=.csv","http")
 
Note that I used H3 to pick up the comma separated list of ticker symbols. I have added an s to the format (=f parameter) to also retrieve the ticker symbol. Just to check that the correct data was retrieved.
 
Edited:
Please note that a new demo was added in which this was all put in place. Please see:
 
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1261134019
 
Back to top
 
« Last Edit: 18.12.09 at 12:25:54 by Gerrit-Jan Linker »  

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