Excel & VBA on a Mac: Look out for Missing Acrobat Distiller

So I just recently completely my first Excel + VBA project for a Mac user. Wow.

It was a Private Project, so I'm not at liberty to share the file with my Insiders subscribers or talk in too much detail about what exactly I was doing, but I did want to relay to my loyal readers the nature of one of the quirks I encountered on this project.

First and foremost is that the script would not run on the Mac. At all. It popped up some obscure message about "Compile error: Can’t find project or library". I struggled long and hard with this. It seemed that some reference was not selected on the Mac user's computer in the Visual Basic Editor --> Tools --> References... menu. Only after having lunch with a good buddy of mine so I could play on his Mac Book Pro, did I see something that my client told me about, but that didn't register immediately with me: She told me that the references checked were...

• Visual Basic for Applications


• OLE Automation


• Microsoft Office 11.0 Object Library


• MISSING: Acrobat Distiller

Now the project initially had involved the possibility of automatically creating some .PDFs from the worksheets (though, in the end we scrapped that aspect), so I misunderstood that she was telling me that "Acrobat Distiller" (which we would have needed to create the .PDFs) was missing from the list. But NOOOOO. She was telling me that it was actually in the list as "MISSING: Acrobat Distiller" and checked.

When I unchecked this reference on my friend's Mac, all was well. The script ran just fine.

When I worked with my client and had her do the same, it worked on her Mac, too.

How about that? Now, there are also some other odd differences between running Excel in PC vs. Mac, such as inconsistency in the amount of print space rows and columns take up, which we had to work out separately, but I thought most interesting was this odd "MISSING: Acrobat Distiller" issue. I'm sure others have faced the same issue, but I never found any references to it on the web when I searched, so here it is.

Have a good day,

Excel_Geek

BTW, (and this is a completely unpaid and unsolicited endorsement), for those of you PC users who who regularly do collaborate with Mac users, and vice versa, I recommend GatherPlace for sharing desktops, etc. When GoToMeeting failed, GatherPlace shined.

Automation Work for eSignal Stock Trading Software User

Recently I was contacted by a technical trader who uses eSignal to scan for and execute stock trades. He had a problem for me to solve, but first I'll need to give some explanation as to what he's doing, though I must admit, he was rightfully secretive about many of the details:

Basically, each morning he uses Yahoo Finance's Stock Screener (an effective and free tool) to identify a group of stocks to watch that day, based upon a certain set of technical factors that he's developed over time (this is one secret part). He actually does this four times, each with a different set of factors. When he's done, he's got four lists of stock tickers, each of which might typically contain 20 to 50 line items, which he can export to a CSV file and open in Excel. Then what he does is create an advanced chart in eSignal for each ticker in each of the four lists, each list having a unique chart configuration and associated with unique and proprietary formulas or "studies" that he's created over time (here's the other secret part) that automatically execute trades on a stock when certain conditions are met.

The problem is that most of the really "good" trade opportunities happen very early in the morning after the opening bell. Meanwhile, he's manually creating charts for 80 to 200 tickers, which takes an hour or so, so he's missed the bulk of the good trade opportunities.

"Excel_Geek, can you help me automate the creation of these charts quickly so I don't miss all the good trades?" Sure.

So what I'd need to do is run down a list of ticker symbols in a spreadsheet, and then create a new chart using the right configuration (of the four he uses) in a whole other software program -- eSignal -- for that ticker. Huh...how do I do that?

So I got online with the technical support people for eSignal and asked some dumb questions. "Is there a way to automate the opening of a large number of advanced charts programmatically?"

"No."

Hmmmm. Now I started reaching, "How about using Shell commands? Can I do it with Shell commands?"

"No."

Really? "'No' like 'you shouldn't do that' or 'no' like 'that is not possible'?"

"It is not possible."

Really? I needed to know who I was dealing with, "Do you have a programming background?"

"No."

That's it then. I'm going to figure out how to do it, and it turns out, that it can be done, and I did it.

What I built is a spreadsheet where this trader can copy-paste in his lists of tickers, click a button, eSignal is fired up, and all of the charts are opened. Instead of an hour or so, it takes about 15-20 seconds. Not bad. He'll get those good trades now!

On the drawback side, we learned that eSignal is a bit resource-hungry, particularly when using advanced charts. With an AMD Athlon 64x2 Dual core processor 4600, 986 MHz, and 2 GB of RAM it starts to choke on around 150-160 charts open at once. Perhaps with some additional RAM and tweaking the paging file settings, we can get some better performance, but in the end, 150 simultaneous opportunities to find profitable trades is not too shabby.

I must disclose to you all that this was a Private Project rather than a $50 Project, so I won't be sharing it with all of my loyal Insiders subscribers. Sorry. However, my client was more than happy to let me generally write about the project here, and he's even expressed interest is licensing it to others if they're interested.

Interested? Shoot me a line.

Later,

Excel_Geek

Return Online Yellow Page Listings to Excel

For a recently requested $50 Project I created a spreadsheet that allows the user to type in the name of a company and an optional state to return a list of online yellow page listings, including company name, address, city, state, zip, and telephone number.

I also created a spreadsheet for this person enabling him to automatically sort by company name, address, city, state, zip, and phone number, as well as automatically apply filters by each of those values, and filter by ranges of zip codes and phone numbers.

How do I get the listing information from the internet to Excel? Well, I wrote a script (using the objHTTP object and regular expressions) that went out to one of the many web-based yellow page services and returned the first page of matches for each company name, along with each associated address and telephone number. After running the code through a list of around 1,800 companies, it retrieved potential matches on roughly half. Not too bad. Beats the heck out of looking up each one manually and copy-pasting the contact info by hand.

All of this took me about 4 hours (programming time and run time included). See the value some of my $50 Project requesters receive!

The script I wrote to get these addresses and phone numbers could be modified to also retrieved websites for those companies listing them, which seemed like around 15-20% of them or so. Also, I could probably also tweak the code to retrieve all company matches for searches done, not by company name, but by business category. That could be valuable, I'd say,

Once a list of company matches were identified, I set up a second spreadsheet to allow this user to automatically sort and apply filters by each of the columns in the list. This could be a simple and effective means of analyzing groups and subgroups of clients, assigning sales or service territories, etc.

Thanks for the project, Ian. I hope you like it.

Excel_Geek