Solved: Perl Conversion

[gmulhall]

Hi,

I have the following piece of working Perl code I'd like to convert to VBA

use Win32::OLE;

my $Broker = Win32::OLE->new('Broker.Application');

my $Stocks = $Broker->{Stocks};
my $ticker = "IBM";

if ($Stocks->Item($ticker)) {
$Stocks->Item($ticker)->{FullName} = "INTERNATIONAL BUSINESS MACHINES";
$Stocks->Item($ticker)->{IndustryID} = 2;
}

"Broker. Application" is the Stock Charting Package Amibroker. It's object model is at http://www.amibroker.com/guide/objects.html

Stocks is a collection with methods Add Item and Remove. The key to Stocks is string like "IBM" for example

In VBA I've coded

Set Amibroker = CreateObject("Broker.Application")
Set Stocks = Amibroker.Stocks

strTicker = "ABC"

If Stocks.Item(strTicker) = True Then
Stocks.Item($ticker).FullName = "ABC COMPANY";
Stocks.Item($ticker).IndustryID = 2;
End If

But I get Runtime error 91 Object variable or with block not set on the line

If Stocks.Item(strTicker) = True Then

Any help appreciated.

Thanks,

Geoff

[Charlize]

What's the purpose of this code ? Is it just financial information of a stockitem named IBM ? If so, maybe that I've got a solution. Fetch daily, weekly and monthly quotes. At least on the computers that i've tested this macro on (in excel 2002, excel 2003).

Charlize

[Ken Puls]

Now, keep in mind that I have no idea how this program works, but I would have tried something like:

[vba]
Set Amibroker = CreateObject("Broker.Application")
Set Stocks = Amibroker.Stocks

strTicker = "ABC"

Set Stock = stocks(strticker)

If not Stock is nothing Then
With Stock
.FullName = "ABC COMPANY"
.IndustryID = 2
End with
End If[/vba]

Assuming that you're trying to set the properties of that stock, that is.

[gmulhall]

Hi Ken - Thanks - I'll give it a go.

Hi Charlize - Again Thanks - if you have a macro which retrieves daily stock info into Excel I'd would be interested.

Regards,

Geoff


vbmenu_register("postmenu_85677", true);

[Charlize]

This zipfile contains two files. Yahoo - Tickers and Model. Put them in C:\Data or another directory. Open Yahoo - Tickers and push the button on the worksheet.

Charlize

ps.: tested with excel 2002 and 2003. But somehow there seems to be an error with the .activate commands by an approver of the kb articles. If someone else wants to give this a go, go ahead and feel free cause I haven't got a clue why on earth this thing won't work. For excel 2000 you must first rename data.csv to work.xls and save it. Then we can close work.xls. For some strange reason you can't close table.csv directly ??? (The code for 2000 isn't included. Maybe later when I have some time left.)

[gmulhall]

Re my problem - Ken's code works (of course !) with

Set Stock = Stocks("ABC")

but not if I put

Dim strTicker As String
strTicker = "ABC"
Set Stock = Stocks(strTicker)

I'm advised changing to

Set Stock = Stocks(Cstr(strTicker))

may resolved by forcing the typing to interface to properly to Amibroker which is coded in C++.

I'll test and confirm.

Thanks again for all the help !

Geoff

[Ken Puls]

Ken's code works (of course !)

I'm glad you thought it was an "of course" thing! LOL!

I'm definately curious to hear the result on the cstr conversion. I'm surprised that setting the variable type to a string wouldn't work, as it's VBA that is interpreting it and applying it against the Amibroker model. Using Dim x as String or converting something by Cstr should yield the same value to my way of thinking. (I may learn something here though.)

[gmulhall]

Hi Ken. I'll advise early tomorrow your time. Thanks again - I've learn't quite a bit from this.

Geoff

[gmulhall]

Have been able to test earlier and using Cstr does resolve the problem !!

Thanks again.

Geoff

[Ken Puls]

I'm sure that there is some logical reason for that, but it seems strange to me. All it's doing is converting a string to a string...

As long as it works, though, that's the key.