Problem executing sub within a sub

Please read the Forum Rules and then wrap your code with Code Tags.

Interesting...

Can we see a piece of the code? I'm interesting in debugging and finding out what's going on.

All,

Since the worksheet is already posted in another forum, I won't repost it here.

Please open Andy's file (649830a.zip) to examine the VBA in the Chart Forum at:

http://www.excelforum.com/showthread...ht=conditional

The stock symbol list is updated by changing cells $P$5-$P$46 which are linked to the combo box.

If you can't reproduce the problem after inserting the call to the GetData sub into the combobox sub, please let me know.

Thanks for any help in solving this.

Art

This was killing it.

Please Login or Register  to view this content.

I don't know why and am not going to look for a solution to a problem that is avoidable. Selecting ranges to gain a reference to a range is best to be avoided whenever possible. You will run into problems...

Replace with:

Please Login or Register  to view this content.

Tom,

Thanks for your reply. I just tried your code without success. When I deleted a stock symbol in the range $P$5-$P$46, I immediately got an error.

When I typed in a new symbol, and selected it using the combobox control, I got a runtime error (Error 1004, "name not valid").

Are we doing something differently?

Art

It ran ok on the file I downloaded. Maybe the Selection.ColumnWidth is now the problem. If this does not work, I'll provide a link to the file.

Please Login or Register  to view this content.

Tom,

Thanks again for your efforts. In order to duplicate your results I did the following:

1) placed your code in the Module 1 as indicated in your last post
2) comment out the existing code as indicated
2) placed a GetData statement in Sheet1 (Raff) to call the GetData sub
3) typed a new symbol in the stock symbol list

The result was that XL crashed on entering the new symbol.

Maybe post your modified file or a link to try it out?

Thanks,

Art

Hi Art.

Try this:

Please Login or Register  to view this content.

Hi Tom,

I think that you are gaining on it. :-) Thanks. This time, when I added your new code plus the previous snippet, I got the following results:

1) When adding a stock symbol to a blank space in the combo box range, there was no error, however, after hitting enter, the program immediately went online. to retrieve data, actually getting data for the current value in the combobox. It should not go online until I select the symbol that was just entered from the drop down list.

2) When I delete a symbol I receive a program error, but XL doesn't crash.

I am not sure what to suggest. Are these code changes that you have posted working on your copy of the worksheet?

Art

Ok. Remove the ListFillRange of the combo and use this code in sheet raff.

Please Login or Register  to view this content.

Hi Tom,

If I delete a stock symbol in the list, I immediately get an error with your new code. Do you have this working? Maybe you could post your file.

Also, I didn't understand your comment "Remove the ListFillRange of the combo."

Maybe that's what I am missing.

Thanks,

Art

Editing the ListFillRange of your combobox is firing the combo's change event. Remove it by clearing the assignment in the combobox's properties.

See the attached.

Tom,

Thanks for posting the file. What I find in using the worksheet:

1) The combobox dropdown doesn't work when the worksheet is first started
2) After a new symbol is typed into a blank cell, the dropdown becomes operable and the new symbol can be selected. The data is updated correctly.
3) Deleting a symbol either the currently selected one in the combobox or or any other, causes a runtime error 1004. The message states that the website could not find the symbol.

Getting there...

Thanks,

Art

Ok... I spent a bit of time really trying to kill this thing and I think it works now.

Download the example that better work...

I made some minor changes to your getdata procedure:

Please Login or Register  to view this content.

Added this to the workbook open routine to do the initial filling if your combobox's list:

Please Login or Register  to view this content.

Sheet Raff code:

Please Login or Register  to view this content.

Tom,

I just tried out your latest workbook. All the desired features appear to work without errors. Thanks so much for your diligence and help!

When I have time, I will scour your code to try to understand what you did.

Thanks again,

Art

Tom,

I spent some more time with your worksheet and couldn't find any bugs - you did a great job. Thanks.

What I realize now though, with a long list of stock symbols, I should have used a listbox using the up/down arrow keys to scroll through the listbox entries. The data would then update when the up or down arrow key is released.

Can the combobox code be ported easily to work with a listbox? The listbox seems to update differently than the combobox. I tried to make some mods to do this without success.

Any thoughts?

Art

Sure. You can use a listbox but where are you going to put it? How many stocks will be displayed at any given time?

Do you want all of the stocks visible instead of the user needing to select from a combobox?

I do not understand what you are trying to do as the combobox seems to be the most efficient use of space.

Tom,

Here's my thinking. I would have two types of worksheets - one with a combobox and the other with a listbox.

On the two listbox worksheets, I will have ~1000 stocks on each ( one for all NYSE and a second for all NASDAQ) in the listbox list. Using the listbox, I can use the keyboard up/down arrow keys to scan down the list and quickly bring up charts of each stock in the list. If the stock meets screening guidelines, I can cull out the symbol and then place it in the combobox list to track.

Performing screening like this with ~2000 stocks every night using the combobox would be tough. The keyboard up/down arrow method with the listbox is far more convenient.

Since the listbox worksheet is only used for screening, only one stock needs to be visible in the window at any given time.

Make sense?

Art

Tom,

On the attached worksheet, I created a listbox which is activated by the keyboard up/down arrow keys.

Additionally, on the worksheet there is a command button which writes the listbox output to a range of cells. The range of course could be modified if needed.

The only function I can't figure out is to have the listbox be active automatically following a command button activation without using a mouse click.

Maybe some of this could be incorporated?

Thanks,

Art

Ok Art. I sort of understand. Using your modified ListBox we could make a small improvement by doing away with the commandbutton and using the enter key or some other key to send the current selection to your list. Focus would not leave the listbox so that would solve the problem of returning focus that we now have.

As for your purpose, I am not clear yet...

"On the two listbox worksheets, I will have ~1000 stocks on each ( one for all NYSE and a second for all NASDAQ) in the listbox list. Using the listbox, I can use the keyboard up/down arrow keys to scan down the list and quickly bring up charts of each stock in the list. If the stock meets screening guidelines, I can cull out the symbol and then place it in the combobox list to track."

Do you physically review each and every stock for certain criteria? If so, can this be automated? What I mean is, run a procedure, go get a cup of coffee, and let the tracking list be auto-generated...

What do you think?

Here is the replacement for the commandbutton. You send your selections from ListBox2 (the modified listbox) to your list using the enter key...
You could easily incorporate use of the shift key (Shift-Enter) to remove an item that was previously entered. I would also include some code to forbid entering accidental duplicates.

Please Login or Register  to view this content.

Yes, that's what I am doing now all manually. Having the listbox worksheet to visually screen out stocks plus the combobox worksheet to track the selected stocks is a huge step forward.

Taking it to the next level, i.e., automating the selection process would be the cat's meow. If you want to try that, I can post the criteria for selection.

It would also be great to have the listbox worksheet and combobox worksheet in the same workbook. That way, I don't need to have two files open.

I'll try out your listbox code using the enter key, that's a good idea, better than the command button. How does one get out of that mode when I want to use the enter key for another purpose?

The option to remove a stock is good as well as the prevention of duplicates.

Thanks for all the great ideas and help!

Art

"If you want to try that, I can post the criteria for selection."

Pease do...

"How does one get out of that mode when I want to use the enter key for another purpose?"

Just tab or select out of the listbox. The KeyDown event will only run when the ListBox has the focus...

Tom,

I'll post a worksheet with some illustrative examples after I do a manual screen.

Art

Tom,

I didn't have time to do a manual screen and populate the list with so stocks that met the criteria that I screen for.

I was able though to quantify the acceptance criteria with three parameters.

The exact values of the three variables in rules criteria amy need to be dialed in, however, if you can set up the search to find stocks meeting these criteria, we can determine if what I select visually matches. Make sense?

Below are the first attampt to quantify the criteria:

1) Close (I8:I*) was >= N6:N* (upper - stddev above regression line) within the last 5 (x) days ---------> x should be variable
2) O8:O* (lower- stddev below regression line) < Close (I8:I*) < N6:N* (upper- stddev above regression line)
3) - 0.02 (y) < O1 (regression slope) < 1.5 (z) --------------> y and z should be variable

I have attached the stock symbols for the NASDAQ and NYSE. This is not the list of all symbols on those exchanges. They are selected based on average price and volume.

Please let me know if you have any questions.

Thanks,

Art

Tom,

I tried out your code to extract symbols from the listbox - that's good, I like it.

Do you have all the information you need from me to start work on the screening code?

Art

Can you break this down into excel functions?

1) Close (I8:I*) was >= N6:N* (upper - stddev above regression line) within the last 5 (x) days ---------> x should be variable
2) O8:O* (lower- stddev below regression line) < Close (I8:I*) < N6:N* (upper- stddev above regression line)
3) - 0.02 (y) < O1 (regression slope) < 1.5 (z) --------------> y and z should be variable

I am less than a layman here...

Tom,

I created the logic in Excel that I described in my earlier post. When you open the attached file, don't change anything right off. I had to adjust the Historical Days Back to get a good example of how the chart would look when meeting the search criteria.

The selection logic is in column V. The 5 day lookback is not adjustable. ( =OR(I7>N7, I6>N6, I5>N5, I4>N4, I3>N3) ) I picked 5 days as a starting value but would like to be able to test this parameter with a different number of days. I don't know know how to program this, maybe you do. :-)

Please let me know if you have any questions.

Thanks,

Art

Hi Art. Please verify my understanding here...

Please Login or Register  to view this content.

Hi Tom,

Seeing the selection criteria logic in different formats is interesting, but yes, your summary of the logic appears to be what I specified.

To take it to the next step, when to actually buy the stock, the logic will change slightly to:

Please Login or Register  to view this content.

Art

Hi Tom,

I didn't know if you are continuing work on automating the stock selection process so I've been playing on my own.

In one of my earlier posts, I was asking about modifying your code in your final worksheet to work with a listbox instead of a combobox.

I tried several times but can't get the listbox to work. Did you ever figure out how to do that?

Thanks,

Art

Hi Art. It's a bit more involved so I will not likely have time until early next week, but I will get to it...

In the mean time, let me know what you come up with so we don't duplicate any work...

Hi Tom,

Do you mean that using a listbox in the worksheet is more involved than the combobox or that the overall process of screening and culling out the screened stocks is more involved?

Anyway, your help on this project is greatly appreciated.

Thanks again,

Art

Hi Tom,

I don't know if it's any help because I don't know what you will program in VBA and what you will do in spreadsheet formulas, but for what it's worth, here's a worksheet where I added code to display whether the stock passes the screening criteria.

Of course I am still going one at a time through the combobox and manually recording the stocks that meet muster.

Art

Hi Art.

The reason I have not bothered yet with the listbox is because I don't know why you will need it. My understanding thus far is that you select all stocks, one at a time, which in turn downloads new data for each stock. If the criteria for "watching" is met for a particular stock, this is added to your "watch list". Seeing that you check each and every stock, why select them? Just loop through ALL of your symbols in a single procedure and let the procedure build the watch list for you. In summary, you end up with two lists. "All stocks" and "stocks you are watching".

My mind toward this is to have two comboboxes. The first for selecting any stock from your master stock list (if this is needed). The other for selecting from your watch list. Add a button, for instance, called "Refresh All". This procedure downloads new data for all of your stocks and builds the watch list based upon the conditions we discussed earlier. This list is used for the second combobox.

If I am not understanding your design, please let me know...

Hi Tom,

I follow your thinking, makes sense. My take on the listbox was to allow manual scanning through the symbol list if desired. Seems that feature would be lost with the combobox, but, I guess that's OK.

Before responding, I went sat down and went through the UI scenario on a piece of paper and came up with the following flow chart.

The big question that I had was whether to create new worksheets for the watch list, buy list, and sell list everyday, or to update a single copy of each every day.

The watch lists, buy lists, and sell lists would each be a combobox.

Please see my simple flow below. It assumes creating a new worksheet every day for the watch list, buy list, and sell list:

Create WatchList
↓
Select Stock group
↓
► NYSE
► NASDAQ
► All
↓
Download
↓
Using selection criteria, write new WatchList to new worksheet
↓
Update WatchLists
↓
Select which Watchlists to update
↓
Create Buylist
↓
Using selection criteria and stocks from WatchList, write new Buylist to new worksheet
↓
Update BuyList
↓
Select which BuyLists to update
↓
Create SellList
↓
Using selection criteria and stocks from BuyList, write new SellList to new worksheet

Let me know what you think.

Thanks,

Art

"I follow your thinking, makes sense. My take on the listbox was to allow manual scanning through the symbol list if desired. Seems that feature would be lost with the combobox, but, I guess that's OK."

That's fine. Using a listbox is not any more complicated than using a combobox. It just takes up more room. It's your design...

"The big question that I had was whether to create new worksheets for the watch list, buy list, and sell list everyday, or to update a single copy of each every day."

Up to you. I don't see why separate worksheets are neccesary if they are functionally the same.


I am under some assumptions here.
Assuming that you download your data once per day for each stock?
After downloading/updating, you make your selections.

The all in one update I was speaking of downloads all data for all stocks in one procedure. From what I know now, the WatchList and BuyList can be created from this same procedure. You have not listed the conditions for creating a SellList. I changed your data retrieval from using web queries to a different method using WINHTTP and an ADO recordset. Functionally, you will not notice much difference though it's a bit faster. However, a recordset can be saved to disk and queried from disk or memory.

So my thought was to click a button which downloads all data, creates the various segregations, and then allows for faster, net-independant access to each stock.

The question now is; can the SellList be created from the self-same data available to create the WatchList and BuyLists?

After this is figured out, you can dipserse your lists to the same worksheet or to separate.

"That's fine. Using a listbox is not any more complicated than using a combobox. It just takes up more room. It's your design..."

I guess that I don't understand why the list box takes up more room. I am not stuck on listbox. Please use whatever is easiest for you. If something needs to change we can address it later.

"Up to you. I don't see why separate worksheets are neccesary if they are functionally the same."

I was thinking for historical purposes, however, this would end up being a lot of stored worksheets wouldn't it. Was thinking that I could go back to review previously created watch lists or buy lists .

"I am under some assumptions here.
Assuming that you download your data once per day for each stock?
After downloading/updating, you make your selections."

Yes, I do this very night. Of course stocks are downloaded serially not in parallel.

"The all in one update I was speaking of downloads all data for all stocks in one procedure. From what I know now, the WatchList and BuyList can be created from this same procedure. You have not listed the conditions for creating a SellList. I changed your data retrieval from using web queries to a different method using WINHTTP and an ADO recordset. Functionally, you will not notice much difference though it's a bit faster. However, a recordset can be saved to disk and queried from disk or memory."

Are you saying download 30 days of data for all ~1700 stocks at once? Isn't that a huge amount of data to store in the worksheet? Maybe I misunderstand.

"So my thought was to click a button which downloads all data, creates the various segregations, and then allows for faster, net-independant access to each stock."

One button click makes life easy. What is "net-independant access to each stock?"

"The question now is; can the SellList be created from the self-same data available to create the WatchList and BuyLists?"

Yes, the sell rule is to sell after a buy either after two days or if the stock price is >= UPPER value.

"After this is figured out, you can dipserse your lists to the same worksheet or to separate."

Correct.

Tom, let me know if I've answered your questions fully.

Thanks,

Art

"I guess that I don't understand why the list box takes up more room. I am not stuck on listbox. Please use whatever is easiest for you. If something needs to change we can address it later."

I'm really getting hung up on this listbox thing...


"I was thinking for historical purposes, however, this would end up being a lot of stored worksheets wouldn't it. Was thinking that I could go back to review previously created watch lists or buy lists."

It's better to store you data away from your UI. The only data that should be in your workbook is the minimum you need to show your charts and figures that are being viewed at that time.


"Yes, I do this very night. Of course stocks are downloaded serially not in parallel."

Ok. Question? You have 1700 stocks of interest. Surely you do not download the past 30 days of data for each stock, every day? Correct? Please explain your dataflow logic... What I mean is, what are you downloading on a daily basis? For which stocks and why for certain stocks?


"Are you saying download 30 days of data for all ~1700 stocks at once? Isn't that a huge amount of data to store in the worksheet? Maybe I misunderstand."

No. Depending on your update frequency, only new data is downloaded. If you are checking daily, then only one day is appended. If you are only looking back 30 days, then your database is trimmed from days beyond. See the example workbook at the end of this post. There are more than a few scripts out there to gather historical data from Yahoo. I made this one because it is more flexible than a standard Excel web query. On a typical home ADSL over wireless, I downloaded 30 days for the 799 symbols in your example workbook in about five minutes.

"One button click makes life easy. What is "net-independant access to each stock?""

My bad terminology. Local access provides faster retrieval of your data. My suggestion is to do all of the downloading at once instead of downloading for each stock as it is selected. You then query from a local source. It makes for a smoother application that will run very fast querying from a local datasource instead of having to wait for a web resource.

This may sound like an over-complication, but it is fairly standard. You are basically using a disconnected recordset to update a local database. The example below simply downloads data from Yahoo, places the data into a recordset, and then copies that to a range. It's only a small step to use the recordset to update a local database which can then be used by your workbook. Your update only gathers data that is missing instead of downloading 29 days of redundant data. In this way, all of your data is always available outside of your workbook but local on your machine. This leaves the workbook managable by only using the data you need for the moment.


More on your flowchart. What is the difference between Create WatchList and Update WatchList? The same for Create Buylist and Update Buylist? SellList?

► NYSE
► NASDAQ

Will this mean that there are separate lists for the two groups? Please provide me with the symbols for NYSE and NASDAQ.

I had the dates set for 1/2/2008 to 6/30/2008. That took around five minutes. After clearing IE cache, I ran it on the last 30 days for the same 799 symbols in under three minutes...

"I'm really getting hung up on this listbox thing..."

So is the listbox in or out?

"Ok. Question? You have 1700 stocks of interest. Surely you do not download the past 30 days of data for each stock, every day? Correct? Please explain your dataflow logic... What I mean is, what are you downloading on a daily basis? For which stocks and why for certain stocks?"

No, no. The chart is updated for the past 30 days up to the current. I don't keep any data beyond the stock that is currently selected for which I have downloaded data via the downloader. Before I got your help and could more easily sort a large group of stocks using the criteria I established, I was doing the process even more manually by visually screening all the stocks. I would then write down the symbols of the "good looking" charts, then one at a time, type the values into the downloader worksheet.(pre combobox)


"No. Depending on your update frequency, only new data is downloaded. If you are checking daily, then only one day is appended. If you are only looking back 30 days, then your database is trimmed from days beyond. See the example workbook at the end of this post. There are more than a few scripts out there to gather historical data from Yahoo. I made this one because it is more flexible than a standard Excel web query. On a typical home ADSL over wireless, I downloaded 30 days for the 799 symbols in your example workbook in about five minutes."

This sounds good

"More on your flowchart. What is the difference between Create WatchList and Update WatchList? The same for Create Buylist and Update Buylist? SellList?"

What I meant is that in my original thinking, there would be a new watchlist that would be saved by date every day. Each day, the watchlists would be updated until all the stocks on the watchlist either move to the buylist or no longer meet the watch criteria. Since there will only be one of each, i.e., a watchlist, buylist, and a selllist this no longer applies. Create watchlist in the flowchart also meant the very first time the watchlist, buy list or selllist is populated.

"► NYSE
► NASDAQ

Will this mean that there are separate lists for the two groups? Please provide me with the symbols for NYSE and NASDAQ."

I kind of wanted the option of keeping them separate. How about simply having an identifier on the lists that identifies the group that they belong to? That would be simpler than having a separate watchlist, buy list and selllist for both. What do you think?

Please check on p.2 of or posts; I posted them in zipped XL files last week. They should be there.

How's progress so far?

Art

Tom,

I checked out your downloader. Pretty cool, and yes, surprisingly fast for all those stock symbols.

Great work.

Thanks,

Art

Art.

You mentioned 1700 stocks. The lists account for 1405. Where are the rest of them?

Tom,

That's all of them. I did a rough count in my head and obviously overestimated.

Art

Hi Tom,

I just happened to think. Although the number of stocks currently is at 1405, do you plan to allow for adding new stocks or deleting stocks currently on the list?

That would be great to be able to do easily.

Art