Conditional Copy-Paste for Stock Trading project

[tbone90s]

Hi everyone,
I'm brand new to programming in Excel, and currently building a system that will help me trade equity options (similar to trading stocks). So far, I've put together the ranking system I want to use based on a live data feed that updates in real-time (during market hours). For every possible trade in my 'universe' (currently about 600), Excel provides a live score (based on my formulas and weightings) as well as prices and other info about the trade (options strikes, symbols, expected return, etc.).

What I'd like Excel to be able to do is keep a separate sheet where a trade (row) that scores over an arbitrary threshold, say, 100 'points,' is recorded as a static line (instead of continuing to dynamically update), like a snapshot of that line. I'll use this list of trades to submit to my broker for trading.
If it was as simple as a 'triggered' copy - paste special - values, that would do just fine. The trigger would also need to see if that trade has already been recorded for the day, so I don't get an infinite list of the same trade.

After a trade makes it into this list, I'll need to compare it to what I already have in my portfolio (which I'll also need to build) so I don't become too heavily weighted in any one trade. (Just thought I'd let you know where this will be going, too).

I'm sure I left out some important details, so please ask away! Thanks for your help!

-Tony

[JeanRage]

Hi,

I would recommend a step-by-step approach ... since you are tackling too many things at the same time ...
Try to stick to your initial deals list, which already requires the combination of an event macro to detect your 100 bp, a macro to avoid duplicates and a macro for the copy pastevalues to another sheet ...

HTH

[tbone90s]

Thanks, Jean!

It's definitely a multi-macro situation, I agree. I thought I would lay out the whole project so everyone who read it could get a feel for where I was trying to go with it.

First, yes, I am trying to figure out how to detect when an opportunity (one row in my worksheet) has a score (already calculated in column D of each row) above a certain threshold (say, 100 points). And the twist is that this information will be updating in real time, so if the macro would run every 1 second (or maybe 2-5 seconds), either adding to the list or not, that would be optimal.

Any ideas on how to attack this one? Thanks guys!

[rwgrietveld]

Tony, This sounds easy but in these cases it would be very helpful if you can provide us with a "before" and "after" situation.

Then this is fixed quickly. Answer this question as well:
Who/What triggers this copy? How is the dynamic data put in excel in the first place?

[tbone90s]

Hi RW,

I'm using Reuters Trader and their DDE service to obtain the live data. I pull this data by providing a list of symbols (one per row) and info codes (one per column). I'm attaching a sample sheet for you. Let me know if the 3 sheets don't answer your questions.

I would like the trigger to be 'automatic' where the macro looks for this opportunity constantly (or every 1-5 seconds) and creates a new entry on the Trading ("after") sheet when the required Score is achieved. This should be a snapshot as opposed to a dynamic link so that the data doesn't change once it is copied to the trading sheet.

Let me know if this makes any sense.

Thank you!

-Tony

[JeanRage]

Hi,

Do you maintain a reference list of your underlying instruments ...?

[tbone90s]

Jean -

Yes, the list of the ~300 underlying symbols is in a sheet in a separate workbook (one of 3 supporting workbooks) that feed numbers into the main workbook. I have one supporting workbook that feeds up to 5 years of daily price history (dynamically updated with the Reuters program) with one underlying symbol per sheet, another supporting workbook with a list of all the possible options strikes for each underlying symbol (one underlying symbol per sheet) using the OptionsXL Add-In, and a third workbook with a little static fundamental info from the hedge fund I work with.

Thanks!

[JeanRage]

Tony,

As you might have guessed it, the easy part is the "filtering" of the potentially valid transactions ... but to avoid being overwhelmed by thousands of transactions on the same underlying instrument, three things are necessary :
1. A reference list
2. The list of already selected transactions , And
3. A rule to determine what to do if, for the same underlying instrument, a new potential transaction pops up ... a bit later ...

HTH

[tbone90s]

As I'm reading about VBA scripting, I think this macro will have to be run on a recalculate, or some other type of event that is constantly occurring. I didn't see that a certain "run every X seconds" was an option, but since the data is real-time, the workbook is recalculating every 1-2 seconds. Do you think that's the best bet?

[rinser]

Hi,

I would probably think of a way to dinamically take off the list the opportunities once the price falls below the threshold and they are no longer profitable.

[rwgrietveld]

OK, Let's start somewhere and work our way to the end:

Assumption:
Every time new data is imported the before sheet re-calculates. This will trigger the following code:

Please Login or Register  to view this content.

For now I just copy the entire row. We will adjust this if this first step is in the right direction.

The 600 rows with score are checked against the threshold: 100 (value in cell I3).
Try this code in your own sheet (600rowsx150columns) and tell me what is good and what is not.

Understand that this code is far from complete !! You'll get duplicates and more, but we'll take it step by step.

Please Login or Register  to view this content.

[tbone90s]

Hi Rinser,

My plan is to have these executed almost instantly using the API from my broker, so at that point, they'll be registered in my portfolio, and no longer in the 'queue.' You're absolutely right, and I definitely should have clarified that intention in my description.

Thanks!

[tbone90s]

Wow, RW, that's a great start!

I adapted it to the big workbook and it's looking good. Here's what's going on:

I don't think the trigger is working. When the worksheet recalculates, it doesn't run. I put that code in module1 in the main workbook just above where I put the rest of the code. Is that right?

By running the code from VBE (F5), it copies the rows that meet the threshold to the specified sheet, adding a column in front for the time stamp.

Since the cells are copied directly (not just the values), on another workbook recalculate, all the relative references break. Not a big problem, since I just need the values from the copy-paste, not the full cell contents.

Other than that, it seems like everything is on track.

Thanks RW!

Please Login or Register  to view this content.

[JeanRage]

Tony,

The Sub CopyPaste() macro goes into the module1 ...

BUT the Worksheet_Calculate() event macro goes into the sheet module ...
( point cursor to the sheet tab name, right-click, select View code to reach the sheet module )

HTH

[tbone90s]

It sure does! Fixed...

Thanks!