psheridan050
52 posts
msg #52863
- Ignore psheridan050 |
7/8/2007 6:06:44 AM
Anyone perform portfolio analysis within Excel? I would like to use Excel to help optimize my filters results after downloading the trade data with indicators. I’m sure using the analysis tool pack is he right way to go but I am unsure which of these tools is best to use and in what situations. Just hoping someone has some experience in this area that they would like to pass along…
|
bielyplafon
42 posts
msg #52864
- Ignore bielyplafon |
7/8/2007 6:56:29 AM
It sounds to me like you don't know what you want to do (to achieve) but you want to do something that would help you.
So what really you want to achieve?
bp
|
psheridan050
52 posts
msg #52866
- Ignore psheridan050 |
7/8/2007 8:25:48 AM
Bielyplafon,
In a way your right. I know intuitively that using Excel with the information provided by StockFetcher can help me analyze filter performance and to see what statistically works and what doesn’t. StockFetcher outputs Entry Indicators for various indicators that you select before running a back test. After the back test is run you can save this information as a .CSV and open it in Excel. Using Excel, I would like to analyze this data for patterns correlations etc that might help me to improve the filters in which I am testing. The problem is building a spreadsheet that will do this for me. I have been reading books on statistics and Excel, and have begun building myself a workbook, but its slow going. I’m look for advice and pointers from people who use StockFetcher and Excel in combination. Or I’m looking for advice on using Excel with stock data in general. Pointers to other websites where I can get this info would be appreciated as well. Or an example spreadsheet would be even better.
|
bielyplafon
42 posts
msg #52867
- Ignore bielyplafon |
7/8/2007 9:26:47 AM
psheridan050,
- I think you want to analyze details on the data that contain much bigger errors (StockFetcher backtest data IMO contain some errors due to limitations how you can define your entry/exit strategy)
- "...analyze this data for patterns correlations..." do you already know what kind of patterns you want to analyze? Did you make your statistical tests in Excel (formulas...)? Data entry into tests shouldn't be such a big problem.
psheridan050,
I don't know what your filters are but I would recomend you to focus on the ideas based on which they are build. Here in the forums are described couple of strategies for trading stocks (stochastic FastK(14); 8% gainers, rsi(2) and positive linear slope, MACD and many more) You can also go to http://stockbee.blogspot.com where I would say is more readable description of some ideas (comparing to usually only technical one here)
Take one idea/filter, understand it and paper-trade. It will give you much more value than back-testing (of course it is not valid if you want to design fully automated trading system). You will be able to practice entries, exits, understanding your emotions... I started with FastK which Muddy described in such a detail that it was easy to follow. (Muddy, I keep saying thank you always when I'm picking stocks for watchlist :o))
Best regards
bp
I think TRO once said "Markets are not about being right or wrong, they are about making money"
|
hscott
34 posts
msg #52869
- Ignore hscott |
7/8/2007 9:45:22 AM
FWIW heres how I do it. Start with a very basic simple filter. Run it and have it include several SF variables that you think might be relevant. Then in the spreadsheet sort on each variable in turn. For example suppose you like OBV. If you sort on OBV you can see how gain/loss varies with OBV. I just take the average of the half of results with low OBV and compare to the half with high OBV but do it as you please. Most of the time a variable will not have much effect on results but sometimes it does. So suppose that you find that low OBV leads to higher return. Then you add a line like "OBV less that xxxx" in your filter, rerun it and see if that helps.
Another idea I had was, instead of starting with a simple filter as I said, I start with a list of say the largest % gainers in the past 30 days or 60 days. Define new variables which contain the variable you are checking at the beginning of your 30 days, like "{set aaa,OBV 60 days ago}. Do this with another list of pretty flat stocks over the period. Merge the 2 results. Then you can see if a particular variable is correlated with higher results.
Here is an example of a filter I constructed using such techniques:
set{ii,intraday intensity(21)}
set{at,ATR(10)}
set{rr,rsi(10)}
set{mf,Money Flow Index(15)}
set{au,Aroon Up(25)}
ii greater than 2000000
at greater than 0.5
rr below 30
au below 5
mf below 30
It works pretty well but only in rising markets
EH Scott
|
lockwhiz
206 posts
msg #52875
- Ignore lockwhiz |
7/8/2007 1:05:09 PM
EXCEL analysis is a great way to combine fundamentals with the technicals....and QMATIX brought it all together with their XLQ.
I found this tool years ago, and it is awesome. The tool actually is able to update your spreadsheet in realtime, with out a manual refresh. It uses COM to do this ... check it out ! XLQ is the name of the tool. You can find it at:
www.qmatix.com
|
guru_trader
485 posts
msg #52905
- Ignore guru_trader
modified |
7/9/2007 11:31:40 AM
Here are some of my favorites add-ins for Microsoft Excel:
XLQ ... this one integrates into cell formulas and will retrieve intraday, daily, weekly, monthly OHLCV data ... you will have to build your own technical indicators with XLQ, or use AnalyzerXL
AnalyzerXL ... this one is loaded with over 140 technical indicators including a neural predictor
|
psheridan050
52 posts
msg #52928
- Ignore psheridan050 |
7/10/2007 6:56:01 AM
Thanks guys for the comments. Ill be looking into XLQ as it looks like an interesting addon to Excel. Does anyone have particular formulas you use in Excel that might be of interest?
|