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

Limitation on MSN's Stock Quote toolbar add-in

Well, it's happened: I have received a project that I will not be able to produce as specified.

Tom wanted a way to update a system of Excel worksheets using MSN's Stock Quote toolbar add-in to refresh current stock quote data. He uses this system to track and produce billing information for his investment management clients' accounts. The problem with the add-in is that it only allows users to get updates every five minutes. I cannot find any way around this limitation on refreshing stock quote data. I've tried everything I can think of: taking over refreshing with VBA commands, using different "instances" of Excel (as opposed to opening multiple files in the same instance of Excel), etc.

In addition, none of the other Excel gurus out there have published anything (that I can find) on the topic about getting around this limitation. Most of them actually balk at the idea of using the toolbar at all, "...when getting data via web querying Yahoo is available..."

In the end, I've had to suggest to Tom two alternatives, neither of which, I'm sure, are all that attractive, given the amount of time, effort, and familiarity he's got with his system as designed now:

1. Redesign the system so that there's one "master" Excel file that contains all current quote data for all client-account-holdings, and then have each of the account worksheets reference data from that file. (This creates only one place that has to be refreshed, so doing one master refresh will ripple down to each of the other worksheets when you click "update links" upon opening them.)


2. Redesign each of the client-account worksheets incorporating automated web queries (using VBA) to enable automatic and ad hoc/on the fly "refreshes" as often as needed.

We'll see what he wants to do. I, of course, won't be asking for $50 until he's satisfied that he's gotten a solution he can live with.

Aany readers out there that have experience with the MSN Stock Quotes toolbar add-in, and have some suggestions for getting around this 5-minute limitation, please send them to me or post a comment on this blog. Thanks in advance!

Excel_Geek

Interesting New $50 Project - Excel as Tool for Investment Management

Tom from New Jersey recently requested a project involving the system of Excel spreadsheets that he uses to track and manage his investor clients' portfolios, as well as provide billing for his services.

He's using Smart Tags to retrieve current stock quote information (from MSN Money Central), but is having some trouble. MSN Money Central only lets him refresh the quotes every five minutes (per spreadsheet). This is somewhat problematic because if a single spreadsheet has more than one worksheet (for example, if a client has more than one IRA account, he might have them each in a different worksheet within the same spreadsheet) he has to wait five minutes between refreshing each client account.

Tom would like to know if there's some "master switch" he can throw to get all stocks in every worksheet updated, even if it's once per day, say at 6 pm or 9 am, or anytime. Further, he's looking for some kind of macro that would automatically open all of these files and refresh all stock quote info at a particular time. Further, he's spent a great deal of time developing his system and has around 200 of these worksheets, so any wholesale change -- like switching the source of his data from MSN Money, etc. -- would be very problematic.

This is going to be a fun one. I love investment-related projects. Makes me want to get my old day trading automator back out and dust it off...memories...

I'll keep you all posted.

BTW, my Excel_Geek Blog_Mail subscriber list keeps growing! I want to, again, pass along my humblest appreciation to all of you who make me feel as though what I'm doing really helps people. Thank you.

Another web query-related $50 Project

James liked the last project so well, he requested another one right away. Thank you, James. For your reward for being a loyal customer, I placed your project at the front of the line (plus I was still in that web query mindset.)

For this project, he had many more different securities to lookup and return values for. I used Excel's built-in web query functionality, again, to do some of them, but also got a regular expression and the HTTP object to directly query some of the pages (ones on Yahoo Finance) directly, which is faster and just plain cooler.

James wanted the query to run every X minutes, which he wanted to be able to set himself. I was banging my head against the wall trying to figure out how to have one button launch a never-ending loop macro that paused for the set duration and have another button accessible to the user to stop the process. None of the methods used by others were working for me (perhaps due to the nature of the web querying going on). James settled for having one button to run the query once, and he'd click it again to run it again.

I believe I've figured out how to do his initial request, though. I was thinking about the problem wrong (as is the case in most/all such instances). Instead I needed a macro that would automatically run on file open, then stop completely for a set duraction (giving the user access to manipulate the file/run other macros), then run again, and repeat. I used the Application.OnTime method, and I think it's pretty slick. One thing, though, is that the time it takes to run all of the queries is included in the delay time, so plan accordingly.

Who else has $50?

New web query-related $50 Project

James (a much-appreciated repeat customer) requested a project on Friday that involved automating a weekly process of going to four different websites (one for each of four different mutual funds) and retrieving the effective 7-day yields for the funds and placing them in a newly added column in an Excel worksheet.

My initial plan was to use VBA to create web calls that returned the text for each of the four pages, then use four different regular expressions to match the data desired and return it, placing in the worksheet. However, I ran into a bit of trouble with the regular expression part of it, so I instead utilized the web query functionality built into Excel to retrieve the entire tables of data on each web page, then simply used cell referencing to place the data where it is to be stored in the worksheet.

In the end the results are identical, though I think performance suffered a bit. Total processing time (on broadband) takes about 5-6 seconds, while if I'd gotten my initial plan to work, it would likely have been 1-3 seconds.

I think I'll take another stab at doing those regular expressions (just for my own sake), and I'll share them with James if I make them work.

Thanks, James.

Everyone else, keep those $50 Project requests coming!

working on repairs to the excel-based trading system

This is taking a bit...trying to optimize the way in which the web queries gather and format the data...stay tuned...

Oh crap! I found a major problem with my excel-based trading system

Turns out that the web query i was using to get the daily data, aggregate it, and then send it to an Access database for storage and complex querying was not using the same OPEN, HIGH, LOW, CLOSE, VOLUME data that is used by the web service (MSN Money's website) that i was using to track the performance of the stocks i was told to buy.

Here's the long and short of it...the OPEN data point i was getting was a calculated value based upon the CLOSE plus/minus the daily change, which is calculated based upon yesterday's CLOSE NOT today's OPEN. Crappy!

That means that the buy signals i was getting, which i wanted to be stocks that gap-opened yesterday and traded on heavy volume, closing near their highs, was not completely true. I was getting more buy signals than I should have been, which explains some of the crappy performances.

I'm getting this fixed and adding in a down-side, balancing trade query...stay tuned...

Decent day (for what the overall market did)

I bought four stocks today, and one i sold at a small loss, but the others were all mildly up, but not to the point where i would sell them (per my rules).

I haven't gotten my downside play rules fine-tuned enough to get them in action yet, but will try to on Monday.

out

Got pummeled today on the trader system

Today was not good. Three trades...three losses...I wiped out the prior two days' gains, too.

I'll be modifying the strategy a bit (not the rules, mind you...i've not given them a chance to prove their on or off). I'm going to apply the inverse of the rules I'm using to identify stocks to get a nice daily uptick to identify stocks that should drop, so that i can short sell them, and insulate myself against overly up or down overall days.

We'll see how that goes.

Another fair day for my Excel-based Technical Stock Trading System

Had two system-dictated buys-then-sells today. One was a loser (NOK) and one was a nice win (SCM). Overall the daily return was over .44%, which again, if replicated over and over, yields one heck of an annual return.

Had a good day with the Technical Trading system

Today's picks generated by my Excel-based technical stock trading system were three out of four winners, generating an after commissions (but before taxes) daily gain of nearly .8%. Heck, if I can repeat this day after day...I'll really be onto something.

Now...of course...a technical trading system is only as good as the rules you create to find stocks and when to buy and when to sell, etc. But the system I've developed can be duplicated and new rules implemented.

I might even share some of the rules tips I'm learning as I go.