# Excel Spreadsheet



## gregcourageous (23 May 2007)

Hello All

Does anyone have a nicely formated template for adding every trade you make? i made myself one but its not the best. 

Cheers


----------



## CanOz (23 May 2007)

Heres one that i found on the net. I've modified mine from this, but its a good start.


----------



## Kaizen (23 May 2007)

Or for a full application have a look at www.otrader.com.au it is a paid application though.


----------



## rowes (23 May 2007)

Hi,

Heres one that i set up, might need a few changes here or there to suit.


----------



## professor_frink (23 May 2007)

And here's one more


----------



## Gar (23 May 2007)

very nice frink


----------



## wayneL (24 May 2007)

professor_frink said:


> And here's one more



Nice Professor!

I have the expectancy #'s on mine too. The only thing I've added is a running expectancy, i.e. the expectancy of the last _x_ number of trades to keep an eye on any divergences from the total.

fwiw


----------



## professor_frink (24 May 2007)

wayneL said:


> Nice Professor!
> 
> I have the expectancy #'s on mine too. The only thing I've added is a running expectancy, i.e. the expectancy of the last _x_ number of trades to keep an eye on any divergences from the total.
> 
> fwiw




Sounds good. How would I go about doing that 

I've spent half of today reworking my futures one so it will break my trades down  into each weekday, with all of the usual stats to go with it. Man what a pain that was 

Getting some pretty interesting results though


----------



## wayneL (24 May 2007)

professor_frink said:


> Sounds good. How would I go about doing that
> 
> I've spent half of today reworking my futures one so it will break my trades down  into each weekday, with all of the usual stats to go with it. Man what a pain that was
> 
> Getting some pretty interesting results though



A make some additional columns offscreen to tally up running wins, losses etc and then a column for the running expectancy.

When I get some time I'll make up some dummy data and post the .xls here


----------



## professor_frink (25 May 2007)

wayneL said:


> A make some additional columns offscreen to tally up running wins, losses etc and then a column for the running expectancy.
> 
> When I get some time I'll make up some dummy data and post the .xls here




Cheers Wayne, it would be most appreciated


----------



## SGB (25 May 2007)

professor_frink said:


> And here's one more




Hi P F

Thanks for the brushup in the use of functions. 
One can never learn enough about xls.

SGB


----------



## professor_frink (25 May 2007)

SGB said:


> Hi P F
> 
> Thanks for the brushup in the use of functions.
> One can never learn enough about xls.
> ...




no worries mate

Glad it's useful


----------



## AndyMc (31 May 2007)

professor_frink said:


> And here's one more




Thanks Professor, looks good and is funcational.


----------



## Damuzzdu (1 June 2007)

Hi,

Here's one that I developed myself.

It relies on a series of IF statements and monitors all sorts of stats for yr trading activity. It made need to be modify to suit, but i can assist in that process.

I've left some phatom trades in the sheet so you can play around with, but if anyone has questions just ask here or PM me.

Cheers
Muzz


----------



## rowes (1 June 2007)

Hi Damuzzdu,

wow that looks pretty cool, will spend a bit of time going over it when i get the chance. looks like you've put alot of thought into it!

could just about start a new thread with software and ideas that ASF members have created or are thinking of, I'm working on little things all the time and i bet lots of others are. Anyone one interested?

cheers


----------



## Bazmate (1 June 2007)

I'm trying to cut and paste the table from COMSEC into my excel spreadsheet but I can't make excel recognise the numbers so that I can make calculations.
There has to be a smart way to do this.... Has anyone come across this and hopefully solved the problem??

TIA

Baz


----------



## CFD (1 June 2007)

In excel use paste special instead of paste and then select unicode text.


----------



## yonnie (1 June 2007)

cant seem to open those xls files.

is that a paid program?


----------



## SteveM (1 June 2007)

.xls files are Microsoft Excel Spreadsheets, You need Microsoft Excell which comes with Microsoft Office


----------



## stockGURU (1 June 2007)

yonnie said:


> cant seem to open those xls files.
> 
> is that a paid program?




Or if you'd like a free, open source solution, download and install OpenOffice: http://www.openoffice.org/


----------



## yonnie (2 June 2007)

thanks guys for your replies.

now I did open excel that comes with Works 8.5.

is that not good enough? its called Microsoft Works Spreadsheet


----------



## CFD (2 June 2007)

Open a blank MS Works spreadsheet. Go to File > Open. In the requester box that opens, go to Files of Type > Excel SS. Then go to Look in >  (find file you wish to open).

Hope this helps. IMHO you would do better to use the link provided by stockGuru to download the OpenOffice programs, as you will find many of the "features" used in excel will not be available in works.


----------



## yonnie (4 June 2007)

thank you CFD, much appreciated......got there

but those examples are not really share trading accounts for the share trader with bank reconciliation.
anybody has an example of one of those?


----------



## ozymick (4 June 2007)

hi all

great infromation in this thread thanks all

I am trading cfd's I have no been able to create a formula to calculate the number of days the position is held for a (current i have to get a calander and count the days)
If anyone can help me it would be greatly apprecaited

Michael


----------



## professor_frink (4 June 2007)

ozymick said:


> hi all
> 
> great infromation in this thread thanks all
> 
> ...




post #4 in this thread- the spreadsheet rowes uploaded had it in there


----------



## Damuzzdu (4 June 2007)

ozymick said:


> hi all
> 
> great infromation in this thread thanks all
> 
> ...




Michael,

Assume that you have yr buy date in column B and sell date in col J, then formula below will calculate it. This will put a blank in the column where you place this formula, and when you enter the sell date the number will appear.

=IF(J41= "","",(J41-B41)+1)

If you wanted to know how many days holdings you could place the formula =NOW() in say G5 (in the top part of the worksheet) and change the formula to this.

=IF(J41= "",G5,(J41-B41)+1) 

Format G5 as dd/mm/yy otherwise it display time as well

Cheers
Muzz

PS the number 41 is the row of course and will change as to where you place the formula


----------



## alston36 (8 June 2007)

Here's another one that I fell over whilst browsing Excel templates.


----------



## fooco (8 June 2007)

fantastic thread, very useful...thx guys


----------



## whatdoyouwant00 (12 June 2007)

Here is one i am working on. 

Do you think it has everything an accountant would need??

I just threw it some fake data to test it...

I have locked the worksheet (no password) so only the unlocked (clickable) cells need you to put info in.

I will eventually put code behind it for easy adding/removing of shares and rolling over financial years and such if/wen i get the time.


regards

Brendon


----------



## somebodyhere (13 June 2007)

what about something that works out how much will be debited / credited from your bank on a particular date? (if your broker does buy/sell offsets)


----------



## macca (15 June 2007)

Hi all,

I have XP with Words and Excel and I want to create a list of stocks that I have researched and/or hold.

I want to be able to have a list of stock codes which I can keep in alphabetical order as I add each days work.

Next to the stock code I want to be able to record my thoughts, comments etc etc

I don’t need the prices in there, I am simply looking to have a record of my research for future reference

I know very little about excel, but I have read that it is very versatile.

I have tried to create something in excel but couldn’t keep them in alphabetical order as I added stocks

Maybe I could download a freebie program somewhere that might do this

Any suggestions ?


----------



## marklar (15 June 2007)

macca said:


> I want to be able to have a list of stock codes which I can keep in alphabetical order as I add each days work.
> 
> Next to the stock code I want to be able to record my thoughts, comments etc etc



Excel is the wrong tool for the job, sounds like you need a database with a little bit of customsation.

m.

PS. For those of you that will mention that Excel is a kind of database, yes, you are correct; however, it's particularly poor at doing what macca is after.  Things are a lot easier if you have the right tool for the job!


----------



## Broadside (15 June 2007)

Sort rows in ascending order (A to Z, or 0 to 9) or descending order (Z to A, or 9 to 0)

Click a cell in the column you would like to sort by. 
Click Sort Ascending  or Sort Descending .


----------



## macca (15 June 2007)

Another thought, I would like to be able to add extra info as required at a later date.

If I type into an existing box in excel, it replaces what was there previously.

Yes, I know I am a dummy 

Just talking to a mate, he seems to recall a program called access ?? Does anyone know of a simple freebie that does basic stuff like this.?


----------



## Broadside (15 June 2007)

macca said:


> Another thought, I would like to be able to add extra info as required at a later date.
> 
> If I type into an existing box in excel, it replaces what was there previously.
> 
> ...




http://www.openoffice.org/product/base.html

also has word processing, spreadsheet which is compatible with Microsoft Office files - and it's free


----------



## rowes (15 June 2007)

> Quote:
> Originally Posted by macca
> I want to be able to have a list of stock codes which I can keep in alphabetical order as I add each days work.
> 
> ...




Excell is great for calculations etc but it is only a spreadsheet not a database.
Access would be your next best bet to setup in, its easy to use, comes free with microsoft office and has alot of cool functions and tools, if you can think of something cool you need there will be a way to do it. but then again i am a little buyest as i develope databases for my job


----------



## YELNATS (15 June 2007)

Damuzzdu said:


> Hi,
> 
> Here's one that I developed myself.
> 
> ...




Very impressive spreadsheet. I'm a long time Excel user and would like to adapt it to my trading within my SMSF, if it's OK with you. Thanks for sharing it with us. regards YN.


----------



## macca (16 June 2007)

Hi all,

I have eventually managed to download Open Office, it kept stopping so I had to find a download manager, then work out how to fly that 

I really am struggling here !!

I have opened up Office Base and read the help files etc in an attempt or four to create what I want but it seems to assume that I know what I am doing, which is not the case 

All I want to do is to create a research file/watch list type of thing where I can make notes for future reference on any stock that I look at. 

I would like to be able to edit each entry at a later date if required.

Sort of like an index card box file with a card with notes for each stock, where I can slot in a new code as I add research.

So I am imagining a list of codes and next to the codes my comments, when I add a code to my list I need it to be in alphabetical order, but also to keep the stock info next to the correct code.

I don't have much hair left, the way this is heading I will be bald by next week 

Can anyone give a simple rundown on what to do, maybe an idiot proof link, when I read the OO help files I don't understand most of it.

In the meantime I shall continue to try and come up with it, maybe a few drinkies might help


----------



## hatrader (22 July 2007)

Damuzzdu said:


> Hi,
> 
> Here's one that I developed myself.
> 
> ...




Hi all,

Fantastic thread everyone!

To Muzz and Professor....really impressive spread sheets. Just wondering how it works when I sell only only a few shares of the same stock at different times???? i.e. Buy 2000 at $5.00 -  then sell say 200 shares at $6.00 then 500 at $7.00, 200 at $8.00 etc. I noticed that on your dummy runs the the buy and sell are of the same holding.

Cheers


----------



## oldpos (23 July 2007)

marklar said:


> Excel is the wrong tool for the job, sounds like you need a database with a little bit of customsation.
> 
> m.
> 
> PS. For those of you that will mention that Excel is a kind of database, yes, you are correct; however, it's particularly poor at doing what macca is after.  Things are a lot easier if you have the right tool for the job!




Hi all,
 I've been reading through this thread with interest.  I think you're on the right track with Excel.   However, you can spend a lot of time trying to come up with an xls sheet that will suit your needs, a lot of time which would be better off spent studying stock situations.   For 3 years now I have been using "Portfolio Manager" by Paul Macgowan of Canberra.  Paul has a great working knowledge of Excel and has his own internet site.   His spreadsheet is fully macro run and records current stock and returns (actual & p.a), has 3 cashbooks, div records, a handy watchlist to which you can add comments, pivot tables, graphs and more importantly, pulls latest figures straight from the ASX.  I could go on for ever. I truly recommend this complex but easliy understood xls.  Much better than you'll ever come up with and is upgraded regularly.
Sells for $30 for 2 years which is a bargain.  You can download the file and use it while restricted to 4 stocks before making any payment.   Check it out.  I'm sure you'll all be impressed.

http://portfolioman.atspace.com/

oldpos


----------



## Kauri (16 August 2007)

One I use for FX GSL position sizing...


----------



## oldpos (19 August 2007)

Kauri said:


> One I use for FX GSL position sizing...




Kauri,

thanks, Are you able to post an unprotected xls.


----------



## Kauri (19 August 2007)

oldpos said:


> Kauri,
> 
> thanks, Are you able to post an unprotected xls.




   The protection password is wriiten at the top of the sheet... haven't got it in front of me at the moment but I think it is Kauri.... 
 Cheers
 .......Kauri


----------



## CanOz (19 August 2007)

Thanks Kauri, i filed that one! 

Heres mine from open book plan.


----------



## Kauri (21 August 2007)

A spreadsheet I use for trading the GSL Indicies.....
 Cheers
..........Kauri


----------



## Kauri (30 August 2007)

OOOPS, just realised the sheet I posted a week or so back for FX was my earlier trial one, attached here is the current correct one, hopefully without errors... sorry for any confusion...  
 Cheers
.......Kauri


----------



## redcorvetteguy (28 September 2012)

whatdoyouwant00 said:


> Here is one i am working on.
> 
> Do you think it has everything an accountant would need??
> 
> ...




nice spreadsheet, how was the end result?


----------



## Steve C (28 September 2012)

Brilliant spreadsheet - does anyone know if expectancy is included in this spreadsheet? It seems to be the only thing missing, or is profit factor another name for expectancy?

Thanks,

Steve



Damuzzdu said:


> Hi,
> 
> Here's one that I developed myself.
> 
> ...


----------



## Steve C (29 September 2012)

Steve C said:


> Brilliant spreadsheet - does anyone know if expectancy is included in this spreadsheet? It seems to be the only thing missing, or is profit factor another name for expectancy?
> 
> Thanks,
> 
> Steve




Edit - I have since read below they are slightly different.
http://m.futuresmag.com/2010/10/01/tradingrsquos-holy-grail-your-calculator

I am an excel beginner - would anyone mind adding in expectancy to the spreadsheet for me?

Greatly appreciated

Steve


----------



## stevier95 (10 October 2012)

Im into medium to long term trading. How should i account for dividends? Can someone please provide a simple and easy to use spreadsheet template that allows me to account for dividends as well and shares acquired through a DRP.
Thanks in advance


----------



## stevier95 (3 November 2012)

Anyone?


----------



## ROE (3 November 2012)

stevier95 said:


> Im into medium to long term trading. How should i account for dividends? Can someone please provide a simple and easy to use spreadsheet template that allows me to account for dividends as well and shares acquired through a DRP.
> Thanks in advance




buy topshare a very good peice of software and will save you hundred of hours in future tax return


----------



## stevier95 (10 November 2012)

ROE said:


> buy topshare a very good peice of software and will save you hundred of hours in future tax return




I'm on a Mac unfortunately


----------



## ROE (11 November 2012)

stevier95 said:


> I'm on a Mac unfortunately




You got parallel and vmware to solve all windows related apps problem 

I'm using Mac as well.. I have 4 PC but I'm slowly move them all to Mac

2 down, 2 more to go


----------



## klumsyboy (27 January 2013)

Hi can anyone design a spreadsheet that does partial sales so I can track the cost as it moves over time?  I buy and sell in parcels so I may not close out a position.

eg I have 100 TLS bought from 2005 @$8, I buy 100 @$4 in 2010 then I sell 150 in 2011, assuming first in first out

then I have 50 left that cost $4 at end of 2011.


----------



## pavilion103 (4 February 2013)

I'm not sure if this can be modified for that purpose or is of use to anyone but this is the one that I created for myself. 

View attachment Trading Software - Master File - MAY 2012.xls


----------



## white_goodman (20 February 2013)

heres an analyzer for those on IB... its free

http://www.rapadashboard.com/


----------



## hawamahal (6 March 2013)

white_goodman said:


> heres an analyzer for those on IB... its free
> 
> http://www.rapadashboard.com/




I downloaded it. looks fancy.

As soon as i start i get a message " Trial period has been expired, please buy a License."


----------



## Wysiwyg (13 September 2017)

Seeking spreadsheet expertise.

Goal - To have all rows change to align with a descending order column request.
Example - on the screenshot there are four columns of time and sales (actual about 4 thousand individual transactions). I want to see Time and Price rows corresponding with Volume and Value


----------



## cynic (13 September 2017)

Wysiwyg said:


> Seeking spreadsheet expertise.
> 
> Goal - To have all rows change to align with a descending order column request.
> Example - on the screenshot there are four columns of time and sales (actual about 4 thousand individual transactions). I want to see Time and Price rows corresponding with Volume and Value
> View attachment 72606



I am not sure whether I am correctly understanding you, but, instead of sorting only one column you might choose to:

(1)  highlight all four columns

(2) from the "DATA" menu  click on "Sort"

(3) tick the "My data has headers" box

(4) in order of preference, select the desired column/s in the "Sort by" field/s (if more than one column is to be sorted, click "add level" for each additional column) 

(5) select "Z to A" in the "Order" field for sorting in descending order

(6) on some occasions, depending upon XL version, and consistency of data formats, the "Options" menu or the "Sort on" fields may need to be utilised.

(7) click "OK"


----------



## Wysiwyg (13 September 2017)

cynic said:


> I am not sure whether I am correctly understanding you, but, instead of sorting only one column you might choose to:



Thanks Cynic but the sort sequence doesn't line up (correspond) the Time/Price with Volume/Value. The Volume/Value in descending order needs to align with Time/Price.

E.g. If I Highlight Volume/Value and click 'Sort Descending' then from High to Low is displayed but Time/Sales obviously does not correspond. The rows must stay aligned so I can see what Time and Price transactions took place from highest Volume/Value transaction downwards.

E.g.2 Highest Volume/Value for the day = 191953 /1791941.49. I need to know what Time and Price the transaction took place and so on for the other 4 thousand transactions.


----------



## cynic (13 September 2017)

Wysiwyg said:


> Thanks Cynic but the sort sequence doesn't line up (correspond) the Time/Price with Volume/Value. The Volume/Value in descending order needs to align with Time/Price.
> 
> E.g. If I Highlight Volume/Value and click 'Sort Descending' then from High to Low is displayed but Time/Sales obviously does not correspond. The rows must stay aligned so I can see what Time and Price transactions took place from highest Volume/Value transaction downwards.
> 
> E.g.2 Highest Volume/Value for the day = 191953 /1791941.49. I need to know what Time and Price the transaction took place and so on for the other 4 thousand transactions.



I may not be understanding you correctly, but if all 4 columns are highlighted for the sort, then the rows will always retain their alignment in the result. But the 4 columns must be highlighed even if only the last two are being selected for the sort.


----------



## Wysiwyg (13 September 2017)

cynic said:


> I may not be understanding you correctly, but if all 4 columns are highlighted for the sort, then the rows will always retain their alignment in the result. But *the 4 columns must be highlighed even if only the last two are being selected for the sort.*



Doh is me. I appreciate your help Cynic.


----------

