Sum if help from the stock traders out there.

[tkthustler]

Probably not as complicated as it sounds but I need some help for those that have a minute.

I have a list of stock returns broken down by weeks and I'd like to get a historical trading profit or loss per month (in %) utilizing a basic trading strategy.

The trading strategy involves trading in the direction of the current weeks price move in hopes to catch the trend of the following week. In trading we can bet against a stock so I can make money on the downside (to "short) and of course to buy the stock (go "long") you would make money if the price goes up. If week 1 is long (cumulative 5 day total from Monday to current price Friday before the market close) I will be long going in to week 2. If week 2 is long I will be long going into week 3. If week 3 was down, I will be short going into week 4. The goal is to get hypothetical P&L (in terms of percentage gain/loss) for each month.

Example for clarification.

Prior month final week -1%
week 1 2%
week 2 3%
week 3 -1%
week 4 2%

Since the final week in the prior month was a negative return, I would "short" the stock or bet against for the upcoming week. Since week 1 was up 2% I would be down -2% at the end of week 1. At the end of week 1 I would also flip the position long since the week ended up positive . Week 2 is up 3% on the week so I would be up 1% total on the month. My position would stay long. Week 3 would be a loss, causing me to be flat month to date and to flip my position to "short". Week 4 would be positive, thus meaning another loss and would mean a -2% total monthly loss and a position flip from short to long heading into week 1 of the next month.

So assuming I have all the weekly return data, what formula can I utilize each month to obtain a weekly profit or loss using this strategy?

Thanks in advance for any help.

 

[igold]

Hi tkhustler,

There is a flaw in the calculations, it cannot be a "straight" line as you suggest.

Say I am long on a stock, I buy 1 share @ $100 = $100 investment. At the end of the week the stock has risen to $150/share. So for the week 1, I am up 50%.

The next week since I was long the prior week, I stay long and the stock then falls in price to $100/share. So for this week 2, I am down -33% ($50 on $150 start). With the price drop I now have the $100 I started with in week 1.

Do you propose that I add the 50% profit in week 1 to the -33% loss in week 2 and come out with a 17% profit when I clearly have the same $100 I started with.

igold

 

[tkthustler]

Well you are exactly right. Man I'm a dumbass, Guess I got caught up in the formula to figure the strategy and didn't even do the math right!

OK so let's say the last week of December stock ABC closes at 100.00 and I buy at 3:59 since it's an up week. I'm now long going into week 1 of January.

So assuming 4 weeks in the month....

Week 1 ending price $110 (I'm up 10% on the week and 10% total)
Week 2 ending price is $120(I'm up 9.09% on the week, 20% total)
Week 3 ending price is $100(down 16.67% in the last 5 days, flat on the month)
week 4 ending price is $105 (price is up 5% on the week but I would have been short as of end of day Friday on Week 3 (down on the week), So I'd be down 5% on the week thus, down 5% on the month)

OK so assuming this example is correct I guess that makes our formula even harder. Would we still use a =sumif?