how to use excel for trading needs/analysis/record keeping

This is a discussion on how to use excel for trading needs/analysis/record keeping within the Trading Software forums, part of the Commercial category; hi, i attempted to use excel to keep a record of all my trades, but it was too difficult to ...

Reply
 
LinkBack Thread Tools Search this Thread
Old Aug 11, 2009, 9:12pm   #1
Joined Mar 2009
how to use excel for trading needs/analysis/record keeping

hi, i attempted to use excel to keep a record of all my trades, but it was too difficult to get to grips with. (ive never used it). So i just used a diff word processor package to keep my record.

Can i ask how some of you are using excel for your trading needs?
Are you using it merely a record keeper, or using it to work out formulas?
eg price action % changes, and other calculations.

All ive managed to do is make a formula to add one cell to another and give a total.

Would it make sense to say write in daily close prices of my stocks/watchlist, in some kind of formula, where it shows me the % increase/decrease over time?


Thanks for any help.
jonboy123 is offline   Reply With Quote
Old Aug 11, 2009, 9:16pm   #2
 
shadowninja's Avatar
Joined Jul 2007
My columns are:
Date | Price | Instrument | Points | Reason For Entry | Other Comments

Keep it simple.
__________________
The markets can stay solvent longer than you can stay irrational.
My sports trading challenge: http://www.trade2win.com/boards/trad...challenge.html
shadowninja is offline   Reply With Quote
Old Aug 11, 2009, 9:21pm   #3
Joined Mar 2009
jonboy123 started this thread
Quote:
Originally Posted by shadowninja View Post
My columns are:
Date | Price | Instrument | Points | Reason For Entry | Other Comments

Keep it simple.
cheers chap.

so youre just using it for record keeping?
no use of formulas or analysis of data?
jonboy123 is offline   Reply With Quote
Old Aug 11, 2009, 11:06pm   #4
 
0007's Avatar
Joined Jun 2005
Having spreadsheet skills is one of the most useful trading techniques that I've acquired and has enabled me to record and analyse my progress (or lack of!) through numerous systems and strategies.

I've found that my requirements change over time and although I thoroughly agree that you should keep it simple, I found that at times I need a more sophisticated analysis and for this purpose it's essential to have a basic understanding of formulae and how spreadsheets work. There will come a time when someone else's ideas for your spreadsheet won't be suitable.

I recommend a cheap textbook (if it explains something you do understand adequately, then it should be ok for the mysteries also - expensive ones only make publishers rich!) and to teach yourself - there are also plenty of free online tutorials.
Here's a Google link for online: excel tutorials - Google Search

I've attached an extract from a simple ss that I used on some spreadbetting evaluation and you can see that even a few simple formulae enable a worthwhile analysis - this one designed to record profitability as a multiple of risk.

You won't regret learning spreadsheets and of course you can also calculate a moonrocket trajectory if you're clever enough
Attached Files
File Type: xls demo - TRADES log.xls (22.5 KB, 2591 views)
__________________
0007 -
"A Gentleman should not be seen before mid-morning unless he is returning home from the night before"

Last edited by 0007; Aug 12, 2009 at 12:58am.
0007 is offline   Reply With Quote
Old Aug 28, 2009, 1:06am   #5
Joined Mar 2009
jonboy123 started this thread thanks guys, havent had a chance to learn it yet, but will.
have also spotted lynda.com...they have financial analysis using Excel tutorials.... sounds good.
jonboy123 is offline   Reply With Quote
Old Aug 28, 2009, 1:23pm   #6
The Staff are paid members that perform various roles such as editorial, advertising, support or technical work.
 
timsk's Avatar
Joined Mar 2002
Hi jonboy123,
Attached is an Excel doc. that I use to record my trades. It holds 200 pages (1 for each trade) plus a 'Month', 'Week' and 'Day' page. I chose 200 as that's the most number of trades I'm likely to make in any one month. You can add more pages - or delete - as required. After I've finished trading, I enter the basic details of each trade on the 'Day' page. I've put a real life example of an actual trade from May 2008. The first column is my ref' No., the rest are all self explanatory - I think? Obviously, you can alter the headings to record the info' that matters to you. At the time, I traded U.S. stocks and ATR was important to me as it dictated my position size. Chances are, you won't want ATR, so you could either delete it or replace it with something else. If you do this, you'll need to manually update the heading in PP's 1-200. (You can do that as a group - you don't have to do it one by one!)

The Tab ref' at the left relates to the page tabs at the foot of the doc. If you click on tab 1, you'll see all the info' that I keyed into the fist row of the 'Day' page appears in tab 1. Pages 2-200 are all blank because I've not keyed any details in the linked rows 2-200 on the 'Day' page. The only info' that I add that's bespoke to each individual page is in the 'Notes' section - and to drop in the chart. All the other info' is on the 'Day' page. At the end of the week, it is easy to tally up the data in the 'Day' page and enter it into the appropriate week on the 'Week' page. At the end of the month, I use all the info' on the 'Week' page to complete the 'Month' page. As you will see, it provides a lot of telling statistics from which you can learn a great deal. One month I noted that I had a positive expectancy on short trades and a negative one on long trades. Had I only traded short that month, I'd have done really well. Based on that evidence, I knew I had to either stop trading long or examine what I was failing to do (or doing wrongly) on the long trades that I was doing well (or better, at least) on the short trades.

At the end of each month, I printy out the entire doc. (except the 'Day' page), together with the broker statements and spiral bind the whole thing into a book. Having the hard copy is useful, I find, as I look at it more often than I would at a doc. buried in the bowels of my computer. Anyway, I hope you find it useful, let me know if anything is unclear.
Tim.
NB. Both xls.doc's are the same - the old one is for anyone who doesn't have a recent copy of the program
Attached Files
File Type: xls TRADE_MASTER.xls (754.0 KB, 2855 views)
File Type: xlsx TRADE_MASTER.xlsx (465.6 KB, 1697 views)
__________________
I'm New To T2W - Where Do I Start? - a must read for anyone new to T2W
I'm New To TRADING – Where Do I Start? - a must read for anyone new to TRADING
The Trading Plan Template - a must read for anyone without a proper TRADING PLAN

Last edited by timsk; Aug 28, 2009 at 1:36pm. Reason: Added extra xls.doc
timsk is online now   Reply With Quote
Thanks! The following members like this post: Charlton
Old Sep 5, 2009, 11:27am   #7
Joined Jan 2006
I use Excel with Interactive Broker's TWS client to monitor all my open positions. With options, knowing where you stand with all your greeks is pretty important, and a spreadsheet is the easiest way to look at it I find.

http://www.interactivebrokers.com/do...kReference.pdf
abottoml is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
track record / performance analysis rab212 Home Trader 2 Mar 1, 2009 7:07pm
Taking your trading record to a proprietary trading house???? DANITFC First Steps 0 Feb 12, 2009 6:51pm
Excel Workbook for Trade Analysis Mrsirplus First Steps 4 Jan 13, 2007 1:02pm
Forex record keeping program? FutureMillionaire? Forex 5 Nov 17, 2006 9:24am

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)