Get Stock Prices in a Spreadsheet

[DannyB]

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.

[sklettke]

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]

[Daredevl07]

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]

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]

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]

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]

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]

[hashky]

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]

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 )'<<notice the passed argument !
'the rest is about building a message box
End Sub

This function is designed to be called from a spreadsheet-array:
Function LookupStockPrices( aCellsWithSymbolsByRows )'<<notice the required argument !
'... converts the data-"flavor" and calls:
aStockPrices = GetStockPrices( aStockSymbols() )
... then it converts back to "spreadsheet-flavour" ...

Called by all the other routines (examples and sheet-function), this one expects another data-"flavour"(different from the "sheet-flavour")as argument:
Function GetStockPrices( aStockSymbols )'<<notice the required argument !
gets the real job done and returns the results

In short:
When you call a macro from a button, menu, shortcut or from the run-dialog, then you do not pass any parameters. It's the same like =MATCH() without arguments in a spreadsheet-cell.
Unfortunately the run-dialog shows all defined routines, even if they are not callable directly without arguments.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[hashky]

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]

[husky55]

@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]

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