Keeping trading stats -- is excel the best way?

C

chowhound888

If you want to compile some simple statistics on your trades in which you can cross-reference performance via categories such as setup, time of day, avg gain/loss per trade etc, is excel the only way to go? This is very simple stuff I want to do I'm assuming, but have virtually no experience with excel.

What I want is to make a daily trade sheet (for example):

TIME STOCK SIZE SETUP GAIN/LOSS

where, after I list all my trades for the day, I could go back later and see for the month additional stats: how setup X performed overall per trade, how X did in the mornings vs afternoons, etc.

Is there a tutuorial someone could recommend for an excel newbie?
 
Excel is what I use. I track my weekly results across all accounts and can drill down on stats over any time frame. I also keep track of all my trades and can also summarize trade results by strategy on any timeframe. Helps me keep track of what is working and what isn't.

http://www.mrexcel.com/forum/index.php

And on this forum I found what they call the CSE formula's. Very powerful array formula's:
http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

I found the CSE formula's and the fn's product and sumproduct to be very useful in calculating the summary data.
 
Instead of Excel, try learning a little bit of Access which is a heck of a lot better in terms of manipulating data. It comes with the MS office suite and there is a good DB example in it called northwind. You could create a " trade table" which includes ff fields.

time of trade/date/trade/qty/price/pnl/ticker symbol/sector

Once you save it you will have rows and rows of data which you can save and manipulate later on.

Down the line, you can create a query to answer questions like.

What is my Pnl from 9:30-10:30 or 3:30 to 4pm
What is my pnl per ticker
What is my pnl per sector
Do I make more $ on shorts first or longs first,etc
What is my avg pnl on tuesdays..or wednesdays.....


As you can see you can't do this in XL. since the spradsheet is static.
 
Good idea. It's hard to beat the flexibility of a good db and someone that knows how to write queries, but you'd be surprised what can be done in excel. I use an Addin called XLQ to pull in additional data on the stocks I trade, such as the volatility, trading range for the time I held, etc, so I can calculate things such as maximum adverse excursion, slippage against my signals for my trades based on end of day screens. And I can calculate these stats by account or strategy for any time frame. Excel is not so static.
 
I use Excel to track all of my trades. I actually spend more time analyzing my trades than I do the markets. A nifty tool is the Filter on Excel. It converts the spreadsheet to a database-like program.

Drag across the titles of your colums and then to go Data --->> Filter --->> AutoFilter. This adds a drop down menu on all those columns and you can pull up all your results on a MACD divergence, for example. You can also sort by ascending/descending. It's a very useful feature.

Another thing you could try is the =vlookup feature. Hit the help button to see how that works. It will take you a little practice to get the hang of that tool if you've never used it before.

Quote from chowhound888:

If you want to compile some simple statistics on your trades in which you can cross-reference performance via categories such as setup, time of day, avg gain/loss per trade etc, is excel the only way to go? This is very simple stuff I want to do I'm assuming, but have virtually no experience with excel.

What I want is to make a daily trade sheet (for example):

TIME STOCK SIZE SETUP GAIN/LOSS

where, after I list all my trades for the day, I could go back later and see for the month additional stats: how setup X performed overall per trade, how X did in the mornings vs afternoons, etc.

Is there a tutuorial someone could recommend for an excel newbie?
More...
 
Thanks all for your suggestions. I asked my gf who has a little more experience in database management and she essentially told me the same thing, either excel or access can do the job.

To me it seems like although it might be overkill at first, access will ultimately be the more user friendly of the 2 in generating "reports" on my trades. But it also sounds like whatever reports access can generate, multiple sorting via column filtering in excel can also accomplish what I would need, with perhaps a few more added steps. I'm told I can export any data from excel into access eventually so my instinct is to start with simple data enrty with excel to just get the basic info down and worry about manipulation later. The only issue that I can forsee down the road is that after inputing thousands of trades over the years, the sorting function might prove to be quite cumbersome to use.
 
Quote from sheepsucker:

Been using excel. But a big problem is when u add and delete columns over time. The excel turns into a mess.
More...
Exactly...which makes it a terrible DBMS...unless you are very good at applying VBA routines to do the work for you in an automated fashion.
Once you start to do everything in Excel by the "front end", you later discover the awful disadvantage of the "easy way out" approach.
 
Quote from syswizard:

Exactly...which makes it a terrible DBMS...unless you are very good at applying VBA routines to do the work for you in an automated fashion.
Once you start to do everything in Excel by the "front end", you later discover the awful disadvantage of the "easy way out" approach.
More...
Yo SysWiz...

Jesus H. Christ... What are you saying... :confused:

Are you saying that Excel is NOT a Database and should not be used as one...

Is That It..

Well...

Hmm...

Ok...

You Are Correctamundo...100% - :eek:
 
You must log in or register to reply here.
Back
Top