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: csv sorting and recalculation (Read 3459 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB demo: csv sorting and recalculation
18.12.09 at 12:00:18
 
litLIB demo: csv sorting and recalculation
 
In this demo I will show how to use csv sorting and formula recalculation without writing any VBA code. I have used the previously published live stock value demo.  
 
In this demo I will use one function call to retrieve all stock data in one go from Yahoo!Finance. Using the sort options of the csv function I will achieve that the best performing stock are sorted to the top of the list. To be able to get the current prices a refresh button was added to recalculate the functions on the sheet. The resulting spreadsheet it attached and a screenshot is provided below. See the text below the screen print for an in depth explanation of the functions used.
 

 
One function call to retrieve the stock information:
It is much faster to retrieve all the stock information in one function call. Yahoo!Finance accepts a comma separated list of values as parameter. Of course I used the litLIB SeparatedValues function to create a comma separated list of the ticker symbols: =SeparatedValues(",",B6:B27). Copying the results and pasting by values gave the needed value. I hard wired it into the call to the csv function for convenience.
 
I selected the range B6 to E27, pressed F2 and entered the following formula and pressed Ctrl+Shift+Enter to make it an array formula:
 
=csv("http://download.finance.yahoo.com/d/quotes.csv?s=agn.as,ah.as,afa.as,akza.as,amt .as,asml.as,bamnb.as,boka.as,dsm.as,fur.as,heia.as,ing.as,kpn.as,phia.as,rand.as,ren.as,rdsa.as,sbmo.as,tnt.as,tom2.as,una.as,wkl.as&f=sl1c1p2&e=.csv","http",,,3,1)
 
Ok, that looks a little involved but it isn't too complex really. The bulk of this function is the url to get the data. If you remove that (I replaced it all by A1 as you can also store the url in a cell) the call reduces to:
 
=csv(A1,"http",,,3,1)
 
Explanation:
first parameter: A1: source for the url
2nd parameter: http: indication we want to retrieve csv from the web
3rd parameter: empty, litLIB defaults to a , field deimiter. That's ok.
4th parameter: empty, litLIB defaults to not skipping any rows.
5th parameter: 3. We will sort column 3, the daily change.
6th parameter: 1: we would like to sort descending (0= ascending)
 
Now the rows in the csv list change according to the column daily change we need to redo the company name list in column A. It cannot remain static as in the previous example. I have cheated a little by creating a small table in O6:P27 with the company names in column O and the ticker symbols in column P. Now I can use the vlookup function in column A to retrieve the company name for the ticker symbol. For convenience I added a name for O6:P27: companies
 
=VLOOKUP(B6,companies,1)
 
This formula will look at B6 wich contains the ticker symbol and return the company name.  
 
This completes the work to make the stock price list.
 
Refresh the formulas and stock prices
To be able to refresh the formulas and to get the latest stock prices I added a refresh button.
 
Go to View-Toolbars-Forms to display the forms toolbar. You can hide it later after creating the button. Select the button control and draw a button on the worksheet. Excel will display the assign macro dialog. Noe that you can assign a macro later by right clicking on the button.
 
Enter the following macro: RecalculateWorkSheet
 
You can make the call more explicit to litLIB by calling:
litlib.xla!RecalculateWorksheet
 
We also added the functions RecalculateWorkbook and RecalculateAllWorkbooks to do a more comprehensive recalculation.
 
With the button in place we have completed the task of creating a spreadsheet with live stock quotes that can be refreshed. All without writing a single line of code.
 
The example file is attached to this forum topic.  
 
See also:
litLIB demo: Live stock prices in Excel  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1261044258
SeparatedValues function
http://www.oraxcel.com/projects/litlib/help/separatedvalues.html
litLIB: Added Recalculation button  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1261132964
Back to top
« Last Edit: 18.12.09 at 12:25:03 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: csv sorting and recalculation
Reply #1 - 18.12.09 at 21:21:44
 
A note for Excel 2007 users.
 
The macro call behind the button is a little more involved unless the litlib.xla is also registered as an addin. It is possible to avoid the registering of the .xla. If you code the following macro you can also call the calculateworksheet routine:
 
Code:
Public Sub recalculate()
    Dim litlib As Object
    Set litlib = Application.COMAddIns("litlib.clsAddin").Object.CallbackObject.Application
    
    litlib.calculateworksheet
End Sub
 


 
Back to top
 
« Last Edit: 18.12.09 at 21:22:31 by Gerrit-Jan Linker »  

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