Any one good knocking up a simple spreadsheet?

Spit · 9 May 2011 at 12:38

I am pretty rubbish at spreadsheets. Need this calculation on a spreadsheet:

I want to be able to alter anything in red. I only need one line for share price and number of shares as the rest are examples only. I will send £5 via paypal to anyone who can knock a nice looking one up. iPad compatible with numbers would be good too but not essential. Thanks

Vonhelmet · 9 May 2011 at 12:42

If I can be arsed, and no one else has done it by tonight, I'll do something for you on my Mac. It'll be about 11pm though.

Spit · 9 May 2011 at 12:56

That would be great

Nix · 9 May 2011 at 12:59

At the risk of asking the obvious, is there a reason you can't you do this for yourself?

Jimbeam3678 · 9 May 2011 at 13:02

I'd start at google but I only just mastered doing a really simple spreadsheet . Never had any real reason to use excel at work or at home so don't have much knowledge about it

Tikkabhuna · 9 May 2011 at 13:04

I'm giving it a go. Not sure how you're working out the "max loss per trade"?

You can send me a trust if you want.

Spit · 9 May 2011 at 13:10

I did have several goes but it just never works out lol.

Max $ loss per trade will always be 2% of account size. $800 with a $40k account.

If I buy 8000 shares at $1.28 my risk cannot exceed $800 so the stop would be 10c below at $1.18.

Pudney · 9 May 2011 at 13:19

This is for MS Excel, you could make it prettier and not use random columns (first one is F):

Uploaded with ImageShack.us

Tikkabhuna · 9 May 2011 at 13:25

http://dl.dropbox.com/u/19325224/spit spreadsheet.xls

Try that. It should work, hopefully.

Edit: No it doesn't. Lol.

Edit 2: The formula I'm using to work out the stop is $800 / No. shares

It works for all your examples in your first post but completely screws up when I try the 8000 shares at 1.28. I take it that's not how you work out the "stop"?

Spit · 9 May 2011 at 13:34

Yeah doesnt work. Its very difficult to get right! I thought I would be able to knock it up in a few mins, an hour later and im like grrrr

Nix · 9 May 2011 at 13:40

Spit said:
I did have several goes but it just never works out lol.

Max $ loss per trade will always be 2% of account size. $800 with a $40k account.

If I buy 8000 shares at $1.28 my risk cannot exceed $800 so the stop would be 10c below at $1.18.

I'm not sure I quite follow how you got $1.18 from the last sentence. Mind explaining a little more?

8000 * 1.28 = 10240 // How do you apply the $800 risk here?

Spit · 9 May 2011 at 13:44

10c of 8000 shares is $800.

If I buy 7500 shares at $1.33 then the stop would be 11c which would be $1.22. Would round up to 11c.

Does that make sense?

Vonhelmet · 9 May 2011 at 13:44

Stop = (account size * risk of trade)/number of shares

And the result for 3000 shares suggests you're rounding that to the nearest 10c.

Pudney · 9 May 2011 at 13:48

vonhelmet said:
Stop = (account size * risk of trade)/number of shares.

Yup, the formula I did, then multiplied by 100 to convert to cents.

Spit · 9 May 2011 at 13:49

Nix said:
I'm not sure I quite follow how you got $1.18 from the last sentence. Mind explaining a little more?

8000 * 1.28 = 10240 // How do you apply the $800 risk here?

8000 * 1.18 = 9440

10240 - 9440 = $800

Tikkabhuna · 9 May 2011 at 13:49

Oh ok, so the stop is like vonhelmet said and for:

If I buy 8000 shares at $1.28 my risk cannot exceed $800 so the stop would be 10c below at $1.18.

You're doing (Price per share - Stop)?

http://dl.dropbox.com/u/19325224/spit spreadsheet2.xls

I added the Price per share minus the Stop.

Spit · 9 May 2011 at 13:59

I think its working

thanks. How do I get your paypal id?

Tikkabhuna · 9 May 2011 at 14:00

That's alright mate. If I didn't do this I would have just been procrastinating another way. Back to sessions and cookies I go.

Nix · 9 May 2011 at 14:03

Spit said:
8000 * 1.18 = 9440

10240 - 9440 = $800

Ah right, seems like a roundabout way of doing things though!

Account: 40,000
Risk at 2%: 800

You want to buy 8000 shares at 1.28 so:

8000 * 1.28 = 10240

To apply the risk:

10240 - 800 = 9440

and: 9440 / 8000 = 1.18

So: 1.28 - 1.18 = 0.10 .: Stop = 10c

With ya.

This is all new to me in case you haven't guessed.

Nix · 9 May 2011 at 14:10

vonhelmet said:
Stop = (account size * risk of trade)/number of shares

And the result for 3000 shares suggests you're rounding that to the nearest 10c.

(40000 * 800)/3000 = 10666.666 :confused:

What am I doing wrong Von? Am I mixing up terms?