Profit/loss spread sheet?

[CityTrader]

I'm spent half the day trying to build a spreadsheet for my impending day trading - I expect to be trading both CFD's & Spreadbetting maybe a total of 10 round trips a day - i.e. 20 individual trades.
I'm trying to build a spreadsheet for each individual account I have ( 4) with tabs for both cfds& spread betting- all feeding through to a master spreadsheet for dailly and running totals for each type of trade.. Has anyone got any pointers or clues ( or any standalone excel packages) that do the trick?

TIA

Wayno

 

[huwwilliams]

P/L Spreadsheet

Wayno,

I don't yet trade myself, but do know spreadsheets.

Not sure of your exact requirement, but suggest you consider a database format, with a pivottable to summarise the data.

I've attached an example (Excel 2000) to give you an idea what I mean.

Regards,

Huw.

 

[CityTrader]

Huw, you have a message.

 

[CityTrader]

The spreadsheet I've constructed allows for me to have a few open positions at any one time, and see which are the open trades. One broker I'm talking to, will charge commission on the opening side of the trade only. I've put in 0.55% just to give me somethign to work on, so, if the initial trade is a purchase it should be added onto price, and if the initial trade is a sale, then deducted. BUT I can't work out how to have the commission only charges one side ( some sort of logical arguement in excel?) thus I have initially allowed for both purchase and sale to ( incorrectly) incluse commission.
What I envisaged was - having a workbook with 3 or 4 of these templates, each for a different broker- thus each morning I would open these spreadsheets- and close each one with that days trading on it - with all 4 spreadsheets feeding through to a fifth spreadhseet which would keep the running totals on CFD's or Spreadbets. Just to make things more complicated - I need to put one of two markers in some trades ( a letter such as a "d" and a "g" ) so I can then having a running total for these trades seperately to the rest ( but still included in the grand total)

 

[huwwilliams]

spreadsheet

Hi Wayno,

I think I've got what you mean, I've amended the first two rows to do what I think you want, with a summary at the bottom.

It's a bit rough, but I think you'll figure out what I mean.

Datatable / PivotTable may still be more elegant, but I'll need a bit more time to put that together.

Take a look and let me know if you have any problems with the next step.

That's half an hours worth of gems you owe me!

Regards,

Huw

 

[CityTrader]

Almost, but not quite, Huw... If I enter a sale into first, and thus put an amount into cell F5, then it doesn't deduct the commission from that price.. Looks like the gems still need to gain some credits from your side?!!

 

[huwwilliams]

Sorry, Wayno, wasn't concentrating!

Is this what you mean?

 

[CityTrader]

Huw, not quite- I think I've mastered my trade input spreadsheet - have agreed with brokers to halve the commission but on both in and out- which makes it a lot easier so I will just oen the templates each morning for each broker and then save them with the days date, . BUT.. I'm fascinated by those pivot tables you used.. is that the answer to my year end spreadhseet?- one in which each of my individual days spreadsheets can feed through to to give mt running year to date totals, that can then be split into spreadbets, cfd;s, monthly totals etc?

Wayno

p.s. let me know when it's pay back time?!

 

[huwwilliams]

Wayno,

Sorry to be so long getting back - been busy.

Yes, I think PivotTables may be a good solution, but you may have to learn a bit about using them. I see you have further requirements and another thread going. I'll take a look later and see if I have any ideas for you.

I think in general, if you want to report on data as a whole, it's better to have it in one range.

Have you considered using Access?

As for Gems, I'm trying to get into trading, with the eventual aim of doing it full time. I could then spend more time on spreadsheet queries! Any pointers on getting started greatly appreciated.

Regards.

Huw.

 

[CityTrader]

Hugh I have an idea - email me? I've pm'ed my email address..