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: historical stock data in Excel (Read 2988 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB demo: historical stock data in Excel
17.12.09 at 13:28:09
 
litLIB demo: historical stock data in Excel
 
To retrieve historical stock data into Excel you can use litLIB's WebPage and , SplitText and csv functions using the data source at MSN. If you don't have litLIB installed yet, get it from here: http://www.oraxcel.com/downloads/index.htm?product=litlib
The spreadsheet file was attached to this message so you can try this out yourself too!
 
Use the =WebPage function to retrieve the web page that includes the MSN historical data. The url to use is at data.moneycentral.msn.com and includes a few parameters: the symbol, the start month and year and the end month and year. The parameter names are respectively Symbol, C1, C6, C7 and C8.  
 
I have setup my spreadsheet with the symbol in B3, the from month in D3, the from year in E3, the end month in G3 and the end year in H3. The function call becomes:
 
=webpage("http://data.moneycentral.msn.com/scripts/chrtsrv.dll?C1=2&C2=&FileDownlo ad=&C9=0&Symbol=" & B3 & "&C1=" & D3 &"&C6=" & E3 & "&C7=" & G3 & "&C8=" & H3)
 
For Microsoft (ticker MSFT) it returns the following data:
 


"""Copyright 2009 Thomson Reuters.""
""Quotes supplied by Interactive Data Real-Time Services.""
""Stock price data provided by Nomura Research Institute, Ltd. Quotes delayed 20 minutes.""
Microsoft Corp (MSFT)
Daily prices
DATE,OPEN,HIGH,LOW,CLOSE,VOLUME
4/28/2000,35.375,35.5,34.125,34.875,78082600
4/27/2000,33.7188,34.9688,33.6875,34.9063,77669800


 
Note that the first few lines do not contain the data (hence the csv function could not be used directly).
 
The first task is to use the SplitText function to split the lines at the end. I used the following formula to achieve this:
 
=splittext(B5,CHAR(13) & CHAR(10))
 
Applying this array formula I selected one column and a range of rows. Then I typed the formula (B5 contains the result of the webpage function) and pressed Ctrl+Shift+Enter.
 
Now we can simply use the csv function to split only those values that contain the csv data. I entered as an array formula in 6 horizontal cells the formula:
 
=csv(B12)
 
This nicely split the csv data into individual cells.
 
The resulting spreadsheet looks like this:
Back to top
« Last Edit: 17.12.09 at 13:42:29 by Gerrit-Jan Linker »  

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