[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 Get Stock Prices in a Spreadsheet Goto page 1, 2  Next
Author Message
DannyB
Moderator

Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

Posted: Thu Feb 03, 2005 11:52 am    Post subject: Get Stock Prices in a Spreadsheet

Previously, I described how to get a stock price using a macro. The technique was refined from an example in the OOo SDK.

A simple macro to get a stock price...
http://www.oooforum.org/forum/viewtopic.phtml?t=4103

As discussed in that thread, here is an example of how to get multiple stock prices in a single internet query. Rather than doing a separate internet lookup on each stock symbol, you pass an array of stock symbols to the function, and it looks up all of their prices in one operation, and then returns an array of the prices.

There are two major functions in this example.
Function GetStockPrices( aStockSymbols )
Function LookupStockPrices( aCellsWithSymbolsByRows )

The function GetStockPrices is the one that you would normally use in a macro. It is easy to call from a macro, and the results are easy to use. It is the heart of the code presented here. Three examples of how to use GetStockPrices are provided.

The function LookupStockPrices is a simple wrapper for the former function. The LookupStockPrices is useful if you want to lookup multiple prices from a spreadsheet.

Here is how you would use the LookupStockPrices function.

1. Create four cells A3:A6 that contain four symbols.
2. Select cells B3:B6
3. With all cells B3:B6 selected, enter the formula: =LookupStockPrices( A3:A6 )
4. Instead of pressing ENTER, press CTRL-SHIFT-ENTER.
This enters an array formula.
Note that array formulas are surrounded by {curly} brackets.

Now all four cells B3:B6 contain the prices for symbols in A3:A6.

Here is the code.

 Code: Sub Main    Example1() '   Example2() '   Example3() End Sub '---------- ' This wrapper function is used when you want to use '  the stock prices macro on a spreadsheet. ' Example: '   1. Create four cells A3:A6 that contain four symbols. '   2. Select cells B3:B6 '   3. With all cells B3:B6 selected, enter the formula:  =GetPrices( A3:A6 ) '   4. Instead of pressing ENTER, press CTRL-SHIFT-ENTER. '      This enters an array formula. '      Note that array formulas are surrounded by {curly} brackets. '   Now all four cells B3:B6 contain the prices for symbols in A3:A6. ' Function LookupStockPrices( aCellsWithSymbolsByRows )    nNumRows = UBound( aCellsWithSymbolsByRows, 1 )    aStockSymbols = DimArray( nNumRows-1 ) '   Dim aStockSymbols( nNumRows-1 )    For i = 1 To nNumRows       aStockSymbols(i-1) = aCellsWithSymbolsByRows(i,1)    Next        aStockPrices = GetStockPrices( aStockSymbols() )        aResults = DimArray( nNumRows-1, 0 )    For i = 0 To nNumRows-1       aResults(i,0) = aStockPrices(i)    Next        LookupStockPrices = aResults End Function Sub Example1()    ' Create an array of the symbols we want.    aStockSymbols = Array( "MSFT", "RHAT" )        ' Look up the prices, and get back an array of prices.    aStockPrices = GetStockPrices( aStockSymbols )        ' Loop over the two arrays, printing each one.    For i = LBound( aStockSymbols ) To UBound( aStockSymbols )       cSymbol = aStockSymbols( i )       nPrice = aStockPrices( i )       Print cSymbol, nPrice    Next End Sub Sub Example2()    ' Look up the prices, and get back an array of prices.    ' Just pass the ARRAY of symbols to the function directly.    aStockPrices = GetStockPrices( Array( "MSFT", "RHAT" ) )        ' Loop over the two arrays, printing each one.    For i = LBound( aStockPrices ) To UBound( aStockPrices )       nPrice = aStockPrices( i )       Print nPrice    Next End Sub Sub Example3()    Dim aStockSymbols( 1 ) As String    aStockSymbols( 0 ) = "MSFT"    aStockSymbols( 1 ) = "RHAT"        ' Look up the prices, and get back an array of prices.    ' Note that we must put parens to pass a dimensioned array as a parameter.    aStockPrices = GetStockPrices( aStockSymbols() )        ' Loop over the two arrays, printing each one.    For i = LBound( aStockSymbols() ) To UBound( aStockSymbols() )       cSymbol = aStockSymbols( i )       nPrice = aStockPrices( i )       Print cSymbol, nPrice    Next End Sub '---------- ' This function looks up the price of a stock symbol from yahoo. ' Pass in these parameters: '      An ARRAY of the stock symbols to look up. '      Each symbol is a string. '   Returns: '      An ARRAY of the stock prices. '      Each price is a number. ' Function GetStockPrices( aStockSymbols )    cSymbols = ""    For i = 0 To UBound( aStockSymbols )       If i > 0 Then          cSymbols = cSymbols + "&"       EndIf       cSymbol = aStockSymbols(i) ' get single symbol from array       cSymbols = cSymbols + "s=" + cSymbol    Next        ' Use this URL to get a stock symbol from Yahoo.    ' For example, this URL...    '   http://quote.yahoo.com/d/quotes.csv?s=RHAT&s=MSFT&f=sl1d1t1c1ohgv&e=.csv    '  would return a CSV file with the prices for both Red Hat and Microsoft.    ' Similarly, we can form a URL for a single stock, based upon the parameter.    cURL = "http://quote.yahoo.com/d/quotes.csv?" + cSymbols + "&f=sl1d1t1c1ohgv&e=.csv"        ' Open up a new spreadsheet from the above URL.    ' Specify the CSV filter with options that decode the CSV format comming back from Yahoo.    ' Specify the Hidden property so that the spreadsheet does not appear on the screen.    oCalcDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0,_       Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_             MakePropertyValue( "FilterOptions", "44,34,SYSTEM,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10" ),_             MakePropertyValue( "Hidden", True ) ) )        ' Get the first sheet of the Calc document.    oSheet = oCalcDoc.getSheets().getByIndex( 0 )        ' Create an array of the prices.    ' The prices are in column B of the spreadsheet (i.e. column 1).    Dim aStockPrices( UBound( aStockSymbols ) ) As Double    For i = 0 To UBound( aStockSymbols )       aStockPrices( i ) = oSheet.getCellByPosition( 1, i ).getValue()    Next    ' Be sure to close the spreadsheet, because it is hidden, and the user cannot close it.    oCalcDoc.close( True )        ' Return the array of prices.    GetStockPrices = aStockPrices() End Function '---------- '   Create and return a new com.sun.star.beans.PropertyValue. ' Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue    Dim oPropertyValue As New com.sun.star.beans.PropertyValue    If Not IsMissing( cName ) Then       oPropertyValue.Name = cName    EndIf    If Not IsMissing( uValue ) Then       oPropertyValue.Value = uValue    EndIf    MakePropertyValue() = oPropertyValue End Function

See also....
Calc Examples
_________________
Want to make OOo Drawings like the colored flower design to the left?

Last edited by DannyB on Sat Feb 19, 2005 12:59 pm; edited 1 time in total
sklettke
Newbie

Joined: 02 Feb 2005
Posts: 2

 Posted: Sat Feb 05, 2005 12:53 pm    Post subject: Thank you so much, DannyB. It works great! Not that it's that much of a problem, but why is the array limited to 200 variables? Thanks again, Scott
DannyB
Moderator

Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

Posted: Tue Feb 08, 2005 7:32 am    Post subject:

 sklettke wrote: why is the array limited to 200 variables?

I don't believe that the macro imposes any limit.

Basic might impose a limit on the maximum length of a string, thus limiting the size of the URL being constructed.

Yahoo might have a limit on the number of symbols it will accept in a single URL.

I suppose the macro could be re-written to process the array in batches of queries with 200 symbols queried per batch.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Daredevl07
Newbie

Joined: 23 Mar 2005
Posts: 2

 Posted: Wed Mar 23, 2005 1:33 pm    Post subject: Hi. I'm new to OO.org but have been a longtime Excel user. Most of my spreadsheets involve stock prices in some way, so this code is extremely helpful. It works great to get prices into the spreadsheet once, but how do you get an updated price? Refreshing the cell values doesn't seem to do it. Is there another macro that I can add that will update the prices? Thanks for the kind help. Todd
DannyB
Moderator

Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

 Posted: Fri Mar 25, 2005 12:39 pm    Post subject: Probably what you want is an uno component that gets the prices and returns a Volatile result. That is, the results keep changing in real time. Even if you don't do anything. The results would keep changing with no user intervention. I don't know when I'll get around to writing something like this._________________Want to make OOo Drawings like the colored flower design to the left?
Daredevl07
Newbie

Joined: 23 Mar 2005
Posts: 2

 Posted: Wed Mar 30, 2005 11:50 am    Post subject: That would be the ideal, possibly overkill for my purposes. I just need a way to refresh prices manually without having to close and reopen the file. Any suggestions?
cd7
Newbie

Joined: 30 Jan 2006
Posts: 2

 Posted: Mon Jan 30, 2006 11:25 am    Post subject: I tried to use your code but I have some problems: i do can run the three examples but I can not use the function (both LookupStockPrices and GetStockPrices) in a regular formula inside a cell. When I try I get the Error 522 in the cell. Can you, or anybody, tell me what I'm doing wrong? (Note. I'm just starting using Ooo Calc)
hashky
General User

Joined: 12 Aug 2006
Posts: 11

 Posted: Sat Aug 12, 2006 4:23 pm    Post subject: Getting Stock Prices in a Spreadsheet I have run the macro examples in this post. I get the Examples 1, 2, and 3 to work. But cannot get the download from Yahoo either. Can anyone tell me why I get "A scripting Framework error occurred while running the Basic script DannyB.Module1.GetStockPrices. Message: Wrong number of parameters." Can you tell me what I need to do to fix it. Ron
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Sat Aug 12, 2006 6:31 pm    Post subject: Re: Getting Stock Prices in a Spreadsheet

 hashky wrote: I have run the macro examples in this post. I get the Examples 1, 2, and 3 to work. But cannot get the download from Yahoo either. Can anyone tell me why I get "A scripting Framework error occurred while running the Basic script DannyB.Module1.GetStockPrices. Message: Wrong number of parameters." Can you tell me what I need to do to fix it. Ron

No, unless you tell us how you call that function. From a button, a cell-function another macro? How exactly (cell-formula, script-event, code)?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
hashky
General User

Joined: 12 Aug 2006
Posts: 11

 Posted: Sun Aug 13, 2006 4:04 pm    Post subject: I don't know enough about OO calc to answer your questions, so I will tell you what I did. I copied the green text (which I assumed to be a macro) into a text editor. I went to Tools/Macros/Organize Macros/OpenOffice.org Basic Macros/Organizer/New. I pasted the text into the new page and called the Macro DannyB. OO called it Module1. The structure is now My Macros/DannyB/Module1. Under Module1 there are several entries: Example1 Example2 Example3 GetStockPrices LookupStockPrices Main MakePropertyValue At the worksheet Sheet1, I entered Stock Symbols CDWC, FISV, HD, and LLL in cells A3 through A6. In B3:B6, I created an Array =LookupStockPrices(A3:A6). I save this in a file called DannyB.ods. Then I click on Tools/Macro/Run Macro and highlight GetStockPrices. That's when I get the error. I also get the error at LookupStockPrices. Ron
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Sun Aug 13, 2006 5:09 pm    Post subject: Thanks. It's clear now. Array {=LookupStockPrices(A3:A6)} works for me in the way you described. At least I get some numbers. Did not prove if they are the right ones. Sub Example1 works without any arguments and you can call it from the run-macro-dialog or some button Sub Example1 '<< no arguments ! 'it looks up the same symbols every time, hard coded here: aStockSymbols = Array( "MSFT", "RHAT" ) 'then it calls a function, passing the array of symbols as argument: aStockPrices = GetStockPrices( aStockSymbols )'<
hashky
General User

Joined: 12 Aug 2006
Posts: 11

 Posted: Mon Aug 14, 2006 7:26 pm    Post subject: Huh? I understand what you are saying, but I don't know enough about Macros and Calc to take any action. If you could give me an example it would help. Where can I find some instructions on Macros? I have a spreadsheet that I downloaded that runs in Excel, but I am trying to wean myself from Microsoft. It is http://www.gummy-stuff.org/Yahoo-data.htm. I want to do something similar with calc. Ron
hol.sten
Super User

Joined: 14 Nov 2004
Posts: 3532
Location: Hamburg, Germany

Posted: Mon Aug 14, 2006 11:55 pm    Post subject: Re: Huh?

 hashky wrote: Where can I find some instructions on Macros?

Take a look here http://documentation.openoffice.org/HOW_TO/. Especially "VBA to StarBasic Cross Reference" might be helpful for you.

With kind regards
hol.sten
husky55
General User

Joined: 26 Aug 2006
Posts: 9
Location: Madison, CT

 Posted: Sat Aug 26, 2006 5:44 am    Post subject: Stock quote web quey @DannyB, I actually did a forum seach in the Calc forum but did not find anything about this specific subject. I have used a web query in Excel for years ( possibly as far back as Office 97) to update stock and mutual fund quotes from the web (pcquote, yahooquote or msn etc..) Both Wordperfect and Excel have this feature although older Quatro Pro (version 9) had problem connecting to the internet (it was looking for a modem while I have broadband). They both use files with extension *.iqy in windows to do a stock web query and has the capability to refresh the data. I wonder if this can be adapted to Calc with modification. I imported an Excel file with a web query for about 25 stocks into Calc but the query was not imported. I realize that Openoffice is a lot more than Windows, but it would be so fantastic to be able to do something I had taken for granted over the years. I know I can do this in a separate applet and copy and paste. But I am hoping for an elegant solution to this. Thanks,
SergeM
Super User

Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

 Posted: Sun Aug 27, 2006 8:25 am    Post subject: DannyB desn't watch this forum since almost 10 months._________________Linux & Windows OOo3.0 UNO & C++ : WIKI http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK In French http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 HoursGoto page 1, 2  Next Page 1 of 2

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group