trading system and filter test

[Nhi]

I am taking a trading course, and my prof ask us to create a trading system
with .5% filter test on Excel. I have no idea where to start. I will really
appreciate for any advice from you. Thank you!
Detail of the assignment: Invest $100 in each of 6 stock. Using 3 years of
daily data, divide this period in half( 1st half=development period, 2nd
half=test period)
In the Development period: create
trading system with .5% filter test. That means whenever price go up
..5%-->sell and hold in cash, wait until price go down .5%-->buy.

 

[JLGWhiz]

I'd like to help you, but I failed investment trading. I lost about $40K in
a two year period playing with the penny stocks. Besides, I have not a clue
what you are trying to do.

 

[Peter T]

Nhi, ask your prof to better explain the question.

JLGWhiz, a salutary lesson. Although I don't live in the US I've had loads
of calls from New York stockbrokers who invite me most insistently to use
their services to buy penny stocks.

Regards,
Peter T

 

[JLGWhiz]

Most of the people hawking the penny stocks work for investment bankers. The
bank own the stocks by virtue of the loans they have made to some company
against those shares of stock. They probably advanced the company 10 cents a
share and they are trying to sell the shares at 25 cents, 150 % gross profit.
They charge you a flat fee for each transaction which seems like a good
deal, since traditional brokers charge a percentage against the value of the
transaction. But the investment bankers don't care if the stock goes down
after they get their money. Of course if it goes up they are happy, because
they make more money on any of the original shares that they have yet to
sell. The point is, their sales people are not true brokers. They are
salesmen. They don't really service your account so you could be left
hanging out there when you should be selling. They tell you not to be too
concerned since you are not making a large investment, but a lot of little
investments add up to a large investment and when the all go bad, you lose a
lot of money. So, if you invest in penny stocks, be sure to track your own
investment. I didn't and I lost a lot of money.

 

[Nhi]

Thanks everyone. The purpose of this assignment is to test the Weak form of
market efficiency.
In first half of the assignment, we have to observe and make the filter
tests for the daily price of 1 and a half year(historical prices) of some
stocks. The filter test means that we have to test with 0.5%, 1%...6%. For
instance , for the .5% filter test, during that 1and a half year, whenever
price went up .5% compare to the previous price, we will make the decision to
sell. If it went down .5%, we will make decision to buy. At the end, we will
compare which filter test maximized our return and it will be our Optimal
Filter.
The 2nd part of the assignment is to test the Optimal Filter we found in
part1 using prices in 1 and a half year after that. Then we have to COMPARE
that return with the return we would get using Buy& Hold strategy(means we
have to hold that stock in 3 years without any trading)

Basically, he asked us to use Microsoft Excel,write some code in Marco for
the assignment, but we have to find out how to write code by ourselves.
That's my problem because I don't know how to write such code.

 

[Nhi]

THIS IS ALL THE HINTS THAT HE GAVE US:

BUSI 4502 – Investment Management
Creating a 1% Filter Test macro in MS Excel

Having trouble sorting through massive amounts of data? This document should
provide some helpful hints for programming a 1% filter macro in Microsoft
Excel. With any luck, taking these steps into consideration, you should be
able to complete what’s required for the assignment.

First off, open your data file. The idea is you want to manipulate a large
quantity of data. You need to create a macro for this.
In Excel, go to the Tools menu, and select “Record New Macroâ€. A popup
box will appear, asking you for information. You can call the macro whatever
you wish, for example ‘FilterTest’. Give it a shortcut command (e.g. Ctrl +
F). This is the keyboard command you’ll use to activate the macro when you’re
finished creating it.
When you click on “Okâ€, the macro will automatically start “recordingâ€. It
will remember your movements and actions you perform (e.g. clicking on
certain cells, sorting data, doing calculations), so remember to have a plan
before you start recording, otherwise the macro won’t work out properly.

For example, your first data entry will be considered your ‘low’ price, and
will be compared to your second data entry, as shown to the right. The second
price is 1.42% lower than the previous price, and is considered the new low.
If you did this calculation while recording the macro, click on the next cell
down (e.g. H4) and stop recording. You can go to the Tools menu, Macro, and
click on “Stop recordingâ€.


Now that your macro is created, you can “start†it by using the keyboard
command. But don’t do that just yet. You still need to add some more commands
– you need to decide if you’re ready to buy or if you’ve already bought in,
when to sell. Click on the Tools menu again, Macro, and the “Macro†command.
This will list all of the macros created in the excel document. Click on the
one you just made, and click on the Edit button. You should see a Visual
Basic coding window popup, similar to the one below. Enter in the
appropriate code, and your filter will be complete! Remember to run the macro
when you’re finished! Good luck!

 

[Peter T]

I kind of get the idea what the exercise is all about but no way near enough
to offer any suggestions as to how to get started. If you have attended all
the classes and paid full attention, I'd say your prof is expecting too much
from his/her students.

I'd also add, in real life never rely on the results of a recorded macro,
which you don't understand, as the basis for making important decisions!

Regards,
Peter T