How to Calculate use of Funds in a Portfolio of Systems

[LeeDanello]

Julian Cohen wrote:

Quote:

Nice spreadsheet but I got zeros in the P/L column

I did with some of my data too but not all of it. I tried using subtotal in Excel and that gives me zeros for the same data so I haven’t quite worked out what is wrong yet.

I think Said has a macro which calculates these numbers but I couldn’t find the formula for it.

[SaidBitar]

Looks like the order of columns are different between me and you this is why it came zero
I will try to fix it

[LeeDanello]

OK tell me how the columns are arranged in your backtester report

[SaidBitar]

Symbol,Trade,Date,Price,Ex. date,Ex. Price,% chg,Profit,% Profit,Shares,Position value,Cum. Profit,# bars,Profit/bar,MAE,MFE,Scale In/Out

[SaidBitar]

or the other problem could be that your excel uses semicolon in the equations, because mine is using comma

here is the file with semicolon

[LeeDanello]

Said Bitar wrote:

or the other problem could be that your excel uses semicolon in the equations, because mine is using comma

here is the file with semicolon

My Amibroker output is the same as yours. The latest spreadsheet (with semi colon) didn’t work as the macro had a run time error 1004. I’m using Excel version 2007. What is the formula meant to be in column B of Date_Analysis sheet?

[SaidBitar]

=SUMIFS(‘BackTest Result’!K:K,’BackTest Result’!C:C,”>=”&Date_Analysis!A2,’BackTest Result’!E:E,”<"&Date_Analysis!A3) this is the formula for column B it is supposed to check the total money invested at each date
the title is wrong it should be money in the market and the column C should be the total P/L for the day

[LeeDanello]

Yeah I think I figured it out. Column B is the sum of the position values taken on a particular day. Your P/L confused me. If I paste this formula in column B “=SUMIF(‘BackTest Result’!E:E,Date_Analysis!A5,’BackTest Result’!K:K)”into the 1st row and copy it to the last row, things start making more sense.

Or if I paste the formula into the macro ActiveCell.Formula = “=SUMIF(‘BackTest Result’!E:E,Date_Analysis!A2,’BackTest Result’!K:K)” it does it automatically with the same result. I like this a lot!

[SaidBitar]

Yes exactly

[RobGiles]

Hi Julian,
Saw your post and found it interesting. Haven’t looked at Said’s spreadsheet, so with that caveat I wanted to pose the following question:

If you’ve allocated a sum of capital, let’s say $100k to a system, but it only utilised 60% of your allocated cash max, how were you to know that at the time that 60% was t be the max cash allocated? The decision in real time you’d have been faced with would be ‘I’ve still got $40k cash allocated to strategy A that I’d love to shove into strategy B, but what if A gives me enough buy signals tomorrow that would result in the residual $40k being absorbed (and therefore unavailable for further allocation too further strategies)?’ The only way that I could see you getting around that conundrum would be to use the equity from existing positions in strategy A to allocate to B via margin loans or the leverage available in IB (same thing really). apologies if this has already been addressed somewhere else in the forum.

[JulianCohen]

I didn’t make it a precise exercise for exactly that reason. Too difficult to work out any potential changes to equity.

I did it by allocating a portion of equity to the ASX MOC system and then the same amount to the Swing system in the US. Let’s call this Portfolio 1. So out of my full equity I would allocate say 40% to Portfolio 1 and 40% to Portfolio 2 (which would be the US MOC and the ASX Swing).

This gives you the leeway you need in case the swing systems get fully loaded, but you will have an idea from stress testing how often that would occur. You can adjust the proportions as you see fit.

Hope that makes sense.

[SaidBitar]

I am doing similar to Julian but in IB i have currently 2 accounts under the same user and I allocate the cash between the accounts and I use one account for US MOC and ASX swing trading and the other account the opposite i.e US Swing Trading and ASX MOC.

Still this does not mean that you will have 100% utilisation all the time but i did not find another way that is simple to manage the strategies and the accounts without getting lost

[RobGiles]

Yep makes sense thanks Julian.

[Author]

Posts