How to export the portfolio equity curve from Amibroker to Excel

Backtesting with Amibroker - Exporting the portfolio equity curve from Amibroker to Excel

 

Amibroker AFL Code Used in the Video:

// This AFL code should be copied into a new AFL Formula and saved in your custom formula directory (Typically C:\Program Files\Amibroker\Formulas\Custom)

// In the video I have called this file "Export System Equity Curve to CSV"

Filter = 1;
eqname = "~~~EQUITY";
if( Name() != eqname ) SetForeign( eqname );
eq = Close;
Cash = Low;

dr = eq - Highest(eq);
AddColumn(eq, "Portfolio Equity");
AddColumn(cash, "Cash");
AddColumn(dr, "Drawdown");

Show Video Transcript +
In this video I'm going to explain to you how you can export your equity curve from a system backtest into a CSV file or into Excel, so that you can analyze it separately in Excel.

Amibroker does lots of things really well. But there's certain types of analysis that you might want to do in your backtesting on the equity curve, that you just can't do in Amibroker, and you need to put the data into a spreadsheet so that you can do the analysis in a more flexible way.

So the first step that you're going to do in order to export your equity curve from a backtest is run the backtest. You can use all of the normal settings that you use for running a backtest, as normal. So you'll see here in the formula bar, I've just backtested a system called Adrian's System. My "From-To Date" I've set from January 1993 to today. I've applied it to a certain group of stocks. I've applied it in this case to the Australian stock exchange and the Hong Kong stock exchange.

So we've run that backtest. As usual, you can click on the reports button here and get the report for the backtest. But what we really want to do now is look at the equity curve in Excel. So the first thing to understand is that when Amibroker runs a backtest, it produces a new symbol, or a ticker called Equity. It's actually called ~~~Equity. So if you're in the symbols pane here, if we just type ~~~, you'll see down here that the symbol called Equity has come up as the filter option. So that contains all of the data that we want to export into Excel.

Now, I've created a formula, which allows us to explore that ticker code, that equity ticker code, and report back the daily equity, the cash balance, and the level of draw-down that is occurring in that system, in that equity curve day by day.

Now, that code is contained on this webpage, and it's called "Export System Equity Curve to CSV.afl", very creatively named. What that does is it looks into the ~~~Equity stock ticker and pulls out the day by day equity balance, it pulls out the cash that you're holding, and it calculates the draw-down for each day in the backtest.

So what we want to do now is run an exploration using this formula.

So the first thing we're going to do is, we've selected export system equity curve to AFL, in the formula bar. Then in the range we can just select all quotes, because we want every bit of data that's in that equity stock ticker. When we choose what stocks to "Apply To", we don't want to apply it to filter because that's going to apply it to all of the stocks that we ran the backtest on. We only want to apply it to current symbol. Because we've clicked on this symbol here, that's the current symbol.

Then what we're going to do is hit "Explore".

You'll see that very quickly we get a number of columns here, four or five columns. The first one is the ticker name, which is always Equity. The second one is the date. Then it calculates portfolio equity, the cash, and the draw-down. Now, there's no data in the first few rows because I started my backtest in 1993. But if you scroll down here, you'll see that each day we get the portfolio equity, the cash, and the draw-down, and that data is reported every single day all the way to the present.

So, how do we get that into Excel?

Pretty simple. Click on one of the rows, press Control A on your keyboard to highlight all rows, and then Control C on your keyboard to copy. Then you can go right across into Excel, and I'll just find a new sheet here, and you can do click in the top left and Control V to paste. There, very simply you have a table of all of the daily equity, cash, and draw-down from your backtest. So you can do all sorts of cool stuff in Excel using this. I'll leave it up to your imagination. That's all for this video. So I hope that was helpful.

See you in the next one.

Bye for now.

Adrian Reid
Founder - Enlightened Stock Trading

Learn to design & backtest your own profitable stock trading systems – Find out about the Enlightened Stock Trading Certification Program today!

Facebook Comments