I spent some time yesterday rebuilding the downloadable templates in my Building an AI financial market model – Lesson V post to be RapidMiner 4.0 compatible. You can now download both the classification and prediction experiement, gold model file (mod), and Excel files in two easy Zip file downloads. Everything should work for RapidMiner now.

Over time I hope to update the lessons I through IV with new screenshots as the GUI on RapidMiner is different than YALE.

***Please note, the information I’m sharing with you is very valuable and FREE, there’s nothing like this available on the Internet that I know of. Please consider becoming an RSS Reader or buying something from my sponsors.***

The spreadsheet I’m about to share with you was the basic backend ETF trend system I developed for live trading. I used the RTQuotesXL and AnalyzerXL modules from TraderXL Pro to download the data in real time and create momentum signals on the fly. Note: clicking on the links will download a 10 day FREE trial version of the software, I suggest you do and fool around with it.

I spent countless of hours building this spreadsheet (ETF_Trend_System_020808.xls) but have abandoned its development for the time being. For the first time ever, I’m making it available for download on this site. I make no guarantees or warranties for this spreadsheet but I ask that if you modify it, change it, update it, or make it better in anyway to please share it with the world. I want this spreadsheets development to follow the GNU license that covers Linux's development.

Download it and play around with it. I hope that it will be of use to you. As always, if you have any questions, please leave me a comment.

Note: You must have TraderXL Pro installed to use this!  I will post more details about the spreadsheet and what each column means later!

A few years ago I got fascinated with Monte Carlo simulations after this application appeared in my 401k plan. At the time they had this “what if” calculator where you enter your age and then the amount you plan to save yearly in the 401k. You’d have to select how you wanted to invest your money, aggressively, middle of the road, or conservatively. After a few seconds of the application running in the background, a mini report would pop up and say:

Mr. Ott, if you invested $10,000 a year in an aggressive 401k portfolio you could end up with an account balance of either $500,000 or $2,300,000 by age 65.

I was intrigued, how did this 401k website know this? A short while later I realized it was a Monte Carlo simulation that was behind it all.

Since that time I started looking around for a good Monte Carlo simulation but come to find that they cost several hundreds to thousands of dollars. Since I’m frugal (cheap), I decided to see if there were any Excel Addin’s out there for free. I did find a few really feature lacking free Excel Addin Monte Carlo Simulations that didn’t impress me too much.

Then I stumbled across RiskAMP and was surprised by the power of this little Addin.

RiskAMP is a feature rich and tightly intergraded Excel Addin that lets you run all kinds of Monte Carlo simulations. It comes with a chart wizard, over 20 simulation functions, and over 35 random distribution functions. You can model simulations to your hearts content, all in the comfort of Excel. Once you run the simulations you can create a simulation results sheet complete with charts, all with a few clicks in the menu system.

Do you want to model at standard Gaussian distribution? RiskAMP does it.

Do you want to model a Pareto distribution? RiskAMP does it!

What about Binomial distributions? Geometric distributions? Multivariate distributions? Power distributions? Yes, yes, yes, and YES! RiskAMP can do it all!

RiskAMP is the primary simulation system I use to model my stop placement in the Forex Market. I won’t tell you how I do it (trade secret) but since I started modeling possible currency volatility outcomes, I’ve been able to place my stops just outside of the volatile price swings and stay in the game. This lead to swinging the probability in my favor and to the much enjoyed success of my $100 Forex Experiment last year.

I’ve also used RiskAMP to model the outcomes in my controversial “When Traders Blow Up” post and I continuously use it to model volatility outcomes for various stocks. Combined with RapidMiner and TraderXL Pro, RiskAMP has contributed greatly to my bottom line over the last year and I look forward to building more “what if” scenarios with it.

Just for fun, I’m attaching a few of the sample spreadsheets that come with the RiskAMP trial. You’ll need the Addin to make it work but you can test out some of the powerful things you can do with it (all Excel spreadsheets).

• RiskAMP Sample Returns
• Correlated Asset Portfolio
• Retirement Portfolio

I suggest my readers take advantage of the RiskAMP free trial to try out the software. I opted to buy the Personal Edition and quickly realized what a steal that was. As always, if you have a question, please leave me a comment. Please subscribe to my feed on the way out.

It’s no secret that I use TraderXL Pro to download the majority of my Stock, Future, and Forex data that I use for RapidMiner modeling. I use the BulkquotesXL module because it’s able to download daily and weekly data from Yahoo and daily price data from the PiFin data source. Usually it downloads the data into separate tabs in your spreadsheet, which is nice but a pain to manipulate if you want to create a summary sheet to load into RapidMiner.

Have no fear, TraderXL Pro has a built in macro called “TickerHeader” that you can run automatically as you download your data into a nice summary sheet. This macro will runs automatically for each asset symbol you list and place it into a data column. The next symbol will be placed in the following column and so forth. You can download my template her: TickerHeader Macro Template

This should cut down, considerably, your data downloading process and get you modeling sooner!

However, if you want to build my spreadsheet from scratch, you have go through the following steps below and set a few parameters in the BulkquotesXL:

Step 1 – Create a new project

Step 2 – Enter your asset symbols into column A (see my example spreadsheet)

Step 3 – Enter the data range to download (columns B and C)

Step 4 – Select the period to daily (column D)

Step 5 – Enter the word “Summary” in column F

Step 6 – Enter the values “A1″, “C1″, “D1″… (Without the ” “. Why you skip B1 will become clear as you read further along)

Step 7 – Set your data source (Yahoo or PiFin). If your downloading data from more than one data source, you have to enter its name in each row.

Step 8 – in the Data Field column enter “C” for close but in cell I3 enter “D;C”. This will place the Date field of the downloaded asset in A1 and then the closing price in B1.

Step 9 – Enter “TickerHeader” into the Autorun Macro column J.

Then click Download/Refresh Data and your done! The TickerHeader macro comes stock with BulkquotesXL module and has made my data downloading life a lot easier.

As always, if you have questions please leave me a comment or email me via my contact page. On your way out, please subscribe to my feed!

Early last year I worked hard on building an Excel based Algorithmic Trading System (ATS) and I made considerable headway using TraderXL Pro and Yahoo real time quotes before I got distracted by the birth of my son. It’s since been ignored and sitting in a directory on my computer but I’m considering continuing its development this year. As part of getting my head around this task again, I decided to share with my readers some tips and tricks I learned so that you too can start building your own Excel based Algorithmic Trading System. I consider myself a power user of Excel and I use the backend modules of TraderXL Pro to do a lot of the basic analytic analysis.

For the more in depth analysis, I write my own macros and then apply them to any downloaded data I have. Tonight I want to touch on an old post again and share with you a trick on how to automate some calculations using TraderXL Pro. (The best part about the TraderXL Pro package is that I can simultaneously download stock data, apply my pre-built modules, and then link it to a real time (or delayed) quote interface. I did this all with “one click” of a button and used it extensively for my ETF Trend system.) I posted a free linear regression slope macro in my popular Build an ETF Trend System post which you could use to build a generic ETF trending system.

The post contained the Excel macro code, a spreadsheet with some ETF price data that readers could download, and that’s about it. You could apply the macro using Excel’s Run function but that’s about all you could unless you had the TraderXL Pro modules installed.  So for this post, I’m going to apply that same macro to the BulkquotesXL module, download new data, and automatically create the linear regression slope data “on the fly” (automating this task can save you hours of hard work if you are downloading hundreds of stock symbols daily so pay attention!), this of course means you have to install TraderXL Pro (trial version) on your system.

Step 1 – Download and install TraderXL Pro (use this package because it has a macro viewer in it)

Step 2 – Download my Excel spreadsheet (MasterDownloadNMT010108.xls) and open it. You should see the TraderXL Pro modules install into Excel

Step 3 – On the BulkquotesXL menu, select Download/Refresh Data. This will download the new data and create the sheets automatically

Step 4 – Copy “MasterDownloadNMT010108.xls!Module2.LinReg” exactly and without quotes into the Autorun Macro column of the BulkquotesXL Settings tab

Step 5 – Select Download/Refresh Data again. This will re-download the stock/ETF data and simultaneously create an 8, 13, and 26 Week linear regression slope columns.

That should do it! You now have over 15 stock/ETF symbols downloaded into separate sheets and a pre-built macro applied to each one. You could then link this data into another spreadsheet or set of calculations that will help you generate buy and sell signals (more on this topic later) for your ATS. In my future Excel based ATS posts, I plan on sharing with my readers how to build bigger macros, transform more stock data, and how to apply it to real time watch list that generates BUY/SELL signals. As with all my other posts, if you have any questions please leave me a comment and don’t forget to subscribe to my feed on the way out!

Long time readers know that I use a few pieces of software on a regular basis to help me build financial models.  One these is TraderXL Pro, a sophisticated Excel Add-in suite, that I rely on extensively.

I primarily use the TraderXL Pro package to download my stock, futures, and currency data, apply technical analysis, run my stock models in real time, fool around with its classifier algorithms, and backtest my strategies. I like TraderXL a lot because I'm a heavy Excel user and develop many of algorithmic models in Excel before I load them into a neural net.  This little package allows me to do a helluva lot with downloaded stock data before I data mine for statistical relationships.

The TraderXL Pro package comes loaded with several Excel add-ins (I bought the whole bunch because it had a Neural Net and Classifier in it) and was surprised by how extensive its technical analysis library was.  This is an all-in-one investment solution for Microsoft Excel. It has 165 technical analysis functions, historical data downloads, real-time quotes, backtesting, neural network-based prediction and classification, and you can download free historical end-of-day stock data, indexes, mutual funds and futures, delayed and real time quotes for stocks, options, indexes and other securities for more than 50 markets worldwide.

The entire TraderXL suite comes with the following Add-ins:

1. AnalyzerXL – A heavy duty technical analytic add-in. You can download data and attach all kinds of technical analysis functions to your price data. It comes with “expert functions” like Cutler RSI or simple ones like volatility calculations
2. BulkquotesXL – This is my mainstay, I use this add-in to download all my stock, futures, and currency data. You can download price data and transform it in different tables, apply technical indicators on the fly, and even automatically generate charts. This is a very powerful data downloading add-in
3. DownloaderXL – Similar to BulkquotesXL and it downloads stock data and generates a nice graph with volume. I very rarely use this one because it doesn’t give the flexibility as BulkquotesXL
4. RTQuotesXL – this is a nice macro, it allows to download delayed (free) or real-time (fee-based) stock quotes from Yahoo or PCQuote. There is the ability to integrate other quote providers as well
5. PredictorXL – This is a neural net add-in that has been redesigned and is REALLY easy to use and pretty powerful.  I really like the redevelopment work on this add-in. Very easy to setup and use.
6. BacktestingXL – This is their backtesting and strategy macro and it works really well. The setup takes a bit of time but once its done, a simple click of the button will generate your returns.
7. ClassifierXL – For ease of use, and decent results, this is one good little classifier algorithm. I’ve used this to segment the S&P500 index and identify similar groupings of data.
8. OptionsXL – I never use this macro but I have downloaded put and call stock option data. I guess this would be handy if you were an option trader.
9. PortfolioXL – A great add-in for keeping track of your positions.

Now you don't have to don't have to download TraderXL Pro and get the entire package, you can download smaller packages to suit your individual needs, for example:

The TraderXL Pro package comes with all the above listed macros (the best package)

The AnalyzerXL package comes with: AnalyzerXL, BulkQuotesXL, DownloaderXL, RTQuotesXL, and PortfolioXL, OptionsXL (second best package)

The DownloaderXL package comes with: DownloaderXL, RTQuotesXL, PortfolioXL, and OptionsXL.

The PredictorXL and ClassifierXL macros come packaged in the NeuroXL suite (good to have it you want to neural net model!)

 Note: These links, with the exception of the NeuroXL suite, will download the trail versions.  Just save them and test it out for 10 days for FREE.

I hope that this article has helped you understand why I like using TraderXL so much. I've been using this package almost daily since about 2004 and even used it to build my ETF Trend System in Excel. I hope you take advantage of the free trials to test it out and as always, if you have any questions, please leave me a comment.

In case you want to supercharge your 10 day free trial, check out my other posts on how to use the macros in TraderXL Pro:

1. Using ClassifierXL to Find the Right Stock to Buy
2. Using TraderXL Pro to Build an Algorithmic Trading System (ATS)
3. Build an ETF Trend System in Excel
4. Build an ETF Trend System in Excel – Part 2
5. Using TraderXL Pro to Download Data for RapidMiner Modeling
6. Calculating Historical Volatility
7. Calling ATR and A Stop Loss In TraderXL Pro

I’m contemplating foregoing the Rapidminer Ebook tutorial update in favor of a Wiki but after laying out the pros and cons for each this morning, I think I might stick with an Ebook (which will take longer to write). What do you think?

Ebook Pros

1. It can be emailed is very portable
2. I can monetize it (big pro)
3. It can be protected for the member section (membership has its benefits!)

Ebook Cons

1. It requires that I update it
2. It isn’t interactive and subject to peer review
3. It will take longer to write

Wiki Pros

1. Rapidminer users can share their templates and tutorials
2. Posts can be peer reviewed
3. The wiki can be updated 24/7

Wiki Cons

1. A wiki is a great idea but in reality I’ll probably be the only one posting on the wiki
2. It’s a pain to maintain
3. There will be two locations (my posts and the wiki) where tutorial information resides.

I’m attaching a typical Gaussian Regression YALE template that I use quite a bit when modeling time series data. This template loads in YALE 3.4 and is compatible in Rapidminer 4.0.

There are some things to note when using this template:

1. Your output has to be numerical
2. You may use an XLS spreadsheet to load your data but you’ll have to reset your ID and Label designations in the ExcelExampleSource operator
3. You must set the number of validations in the XValidation operator to what’s appropriate for your experiment
4. You must select a directory where to save your MOD file in the ModelWriter operator

That’s it and have fun modeling with this experiement, it works really well for Forex! :)

Gaussian Regression Template.zip

I’ve decided to make some of my YALE templates that I use available to my readers. Most of these templates will be YALE 3.4 format and should be RapidMiner 4.0 compatible. Contained in the zip file below is the XML file and AML file. If you want to save the information you’ll have to add a Modelwriter Operator.

This template is for a generic Evolutionary Feature Weighting experiment. You could apply this to fundamental or technical stock data or whatever tickles your fancy. For more discussion on this topic, see my post on Using Genetic and Evolutionary Algorithms to Build a Trading Model.

Template: Evolutionary Feature Weighting Template.zip

For those of you who don’t like using YALE, now called RapidMiner, I offer you neural nets in Excel. A few years ago I came across a slow, but really cool neural net application written in Excel by a PhD student, Angshuman Saha. I’ve used this little spreadsheet to make quicky models and see curve relationships. It’s easy to use and the instructions are straight forward.

Check out the author’s website, he offers a classification spreadsheet and other fun models.