Microsoft Excel Q&A

[gav]

Hi Excel gurus,

I'm not even sure how to do this at all, let alone trying to get Excel to do it for me...

Calculating my portfolio open equity profit/loss, MaxDD, etc. is simple enough. But what is the best way to calculate these figures when I've added extra capital along the way?

I'm not very good at explaining this, so I will try to illustrate with an example:
Let's say I start with $20K and after 12 months I'm up 20% (but after 10 months I was up 30%, so 20% is not my equity high). Then I add an extra $10K to my account. I now have $34K (20K+20%)+$10K. Then 6 months later (18 month mark) I am down to $26K. My first thought is that my drawdown would be 23.5% ($27K/34K).

But this doesn't seem accurate to me as it does not reflect that $34K is only my equity high due to me adding extra capital. It also doesn't take into consideration that that before I added the extra $10K, my profit had already fallen from 30% (10 month mark) to 20% (12 month mark).

What is the best way to:
1. Calculate my drawdown in the example above
2. Continue to calculate my open equity % +/- when I add extra capital along the way (eg. adding extra capital every 12 months or so)
3. What formula/s can I use to do this in Excel?

 

[Huskar]

Hi Excel gurus,

I'm not even sure how to do this at all, let alone trying to get Excel to do it for me...

Calculating my portfolio open equity profit/loss, MaxDD, etc. is simple enough. But what is the best way to calculate these figures when I've added extra capital along the way?

I'm not very good at explaining this, so I will try to illustrate with an example:
Let's say I start with $20K and after 12 months I'm up 20% (but after 10 months I was up 30%, so 20% is not my equity high). Then I add an extra $10K to my account. I now have $34K (20K+20%)+$10K. Then 6 months later (18 month mark) I am down to $26K. My first thought is that my drawdown would be 23.5% ($27K/34K).

But this doesn't seem accurate to me as it does not reflect that $34K is only my equity high due to me adding extra capital. It also doesn't take into consideration that that before I added the extra $10K, my profit had already fallen from 30% (10 month mark) to 20% (12 month mark).

What is the best way to:
1. Calculate my drawdown in the example above
2. Continue to calculate my open equity % +/- when I add extra capital along the way (eg. adding extra capital every 12 months or so)
3. What formula/s can I use to do this in Excel?

The best way to account for additions or subtractions to your portfolio is by treating yourself like a mutual fund and calculating your time weighted internal rate of return (TWIRR). Although it is a mouthful it is not very complicated once you get the hang of it. Motley Fool wrote an article explaining how to configure it in excel much better than I could: see http://www.fool.co.uk/stockideas/2005/si050720.htm. Not too sure how to adapt to your specific request of drawdown however...

 

[gav]

The best way to account for additions or subtractions to your portfolio is by treating yourself like a mutual fund and calculating your time weighted internal rate of return (TWIRR). Although it is a mouthful it is not very complicated once you get the hang of it. Motley Fool wrote an article explaining how to configure it in excel much better than I could: see http://www.fool.co.uk/stockideas/2005/si050720.htm. Not too sure how to adapt to your specific request of drawdown however...

Many thanks Hesking, that article appears to be what I'm after

 

[gav]

Motley Fool wrote an article explaining how to configure it in excel much better than I could: see http://www.fool.co.uk/stockideas/2005/si050720.htm. Not too sure how to adapt to your specific request of drawdown however...

Since the excel spreadsheets in the link above don't work, I thought I'd attach mine here (using hypothetical data as an example) in case someone else wants to use it.

 

[Gringotts Bank]

I'd like some help with excel please.

How do I create a cell with a function that does the following:

If A1>10 and B1<5 and c1>20, then make the cell (D1) read "BUY"?

Some sort of nested If function?

Pixel, you there/??

 

[Nortorious]

I'd like some help with excel please.

How do I create a cell with a function that does the following:

If A1>10 and B1<5 and c1>20, then make the cell (D1) read "BUY"?

Some sort of nested If function?

Pixel, you there/??

Hi GB,

Will look into it, my thoughts are that it would be some sort of IF function but could also be something like a scenario. Excel will do it, just need to figure it out.

I'll have a play tonight and see what I can do.

Kind regards
Nortorious

 

[rryall]

I'd like some help with excel please.

How do I create a cell with a function that does the following:

If A1>10 and B1<5 and c1>20, then make the cell (D1) read "BUY"?

Some sort of nested If function?

Pixel, you there/??

=IF(A1>10,IF(B1<5,IF(C1>20,"BUY",""),""),"")

 

[bellenuit]

I'd like some help with excel please.

How do I create a cell with a function that does the following:

If A1>10 and B1<5 and c1>20, then make the cell (D1) read "BUY"?

Some sort of nested If function?

Pixel, you there/??

I would think a nested IF is the way to go. I use Apple's iNumbers instead of Excel, but I think the IF syntax is the same in both.

Basically IF(test, true, false) where true and false represents the actions to be taken if test is true or false respectively.

So you could build up your nested IF in 3 stages. First start with the last test and put the following IF in cell D1....

IF(C1>20,"BUY"," ")
this puts the text BUY in D1 if C1>20, otherwise it puts a blank there.

Then nest that IF within another IF using the middle test, where it is the true action and again a blank as the false action.

IF(B1<5, IF(C1>20,"BUY"," "), " ")

So both tests have to be true for "BUY" to be placed in D1, otherwise blank.

Finally nest this IF within another IF that uses the first test, again it being the action to take if true and blank if false.

=IF(A1>10, IF(B1<5, IF(C1>20,"BUY"," "), " "), " ")

You don't have to build D1 up this way, but it helps to structure it properly in your mind.

I have tested this in iNumbers and it works, so it should probably work the same in Excel.

 

[Nortorious]

I would think a nested IF is the way to go. I use Apple's iNumbers instead of Excel, but I think the IF syntax is the same in both.

Basically IF(test, true, false) where true and false represents the actions to be taken if test is true or false respectively.

So you could build up your nested IF in 3 stages. First start with the last test and put the following IF in cell D1....

IF(C1>20,"BUY"," ")
this puts the text BUY in D1 if C1>20, otherwise it puts a blank there.

Then nest that IF within another IF using the middle test, where it is the true action and again a blank as the false action.

IF(B1<5, IF(C1>20,"BUY"," "), " ")

So both tests have to be true for "BUY" to be placed in D1, otherwise blank.

Finally nest this IF within another IF that uses the first test, again it being the action to take if true and blank if false.

=IF(A1>10, IF(B1<5, IF(C1>20,"BUY"," "), " "), " ")

You don't have to build D1 up this way, but it helps to structure it properly in your mind.

I have tested this in iNumbers and it works, so it should probably work the same in Excel.

Great job guys!

I have adjusted to include a DO NOT BUY

=IF(A1>10,IF(B1<5,IF(C1>20,"BUY","DO NOT BUY"),"DO NOT BUY"),"DO NOT BUY")

 

[McLovin]

You could just do

IF(AND(A1>10,B1<5,C1>20),D1="BUY",D1="DON'T BUY")

It's a bit neater.

 

[bellenuit]

You could just do

IF(AND(A1>10,B1<5,C1>20),D1="BUY",D1="DON'T BUY")

It's a bit neater.

Yes that's neater. AND is a function I wasn't familiar with. Thanks. In iNumbers, I must leave out the two "D1=" to get it to work. I must put the formula in cell D1 though.

IF(AND(A1>10,B1<5,C1>20),"BUY","DON'T BUY")

 

[pixel]

Yes that's neater. AND is a function I wasn't familiar with. Thanks. In iNumbers, I must leave out the two "D1=" to get it to work. I must put the formula in cell D1 though.

IF(AND(A1>10,B1<5,C1>20),"BUY","DON'T BUY")

Depending on your Excel version, you can find all functions by simply clicking on the fx symbol at the head of the formula bar.
But I would always place constants like your 10, 5, 20 above the cells that I want to compare them with. Assuming you have several sets of numbers A,B,C, you can then build a table and fill the formulae down by auto-fill.
I also tend to store simple interim results in their own columns. That makes it much easier to understand the logic after a few weeks or months when the invariable question comes "Why on Earth did I do that?"

So, here is a snapshot of the way I would organise the table:



(You can "hide" the columns 5-7 if you ever ptint or share your results; and don't hold back with meaningful commentary and column descriptors.)
Below, I show the formulae:



PS: Instead of A1 notation, I prefer R1C1 format; but that's a matter of preference.

 

[Gringotts Bank]

This is great! Thanks everyone for the help.

Anyone know how I would go about using a macro to identify any instance of 'BUY' in column D and copy/paste that into notepad? I would like the macro to run continuously so that by end of day my notepad has a list of all stocks that appeared as buys during the day. Could be tricky?

 

[pixel]

This is great! Thanks everyone for the help.

Anyone know how I would go about using a macro to identify any instance of 'BUY' in column D and copy/paste that into notepad? I would like the macro to run continuously so that by end of day my notepad has a list of all stocks that appeared as buys during the day. Could be tricky?

That will be tricky, if you want to run that Excel macro continuously.
It would be much easier if your trading software allowed you to set formula-based alerts. For example, fill a new column of your watchlist with that buy trigger and, instead of simply saying "Buy", make it flash or go ding-dong.

In the Market Analyser, for example, you would write your formula as a Pascal-script and add it to your Indicator Alerts. The cumbersome bit is: You have to create one alert list entry per watchlist element.



Last alerts triggered on the days marked below:

 

[McLovin]

This is great! Thanks everyone for the help.

Anyone know how I would go about using a macro to identify any instance of 'BUY' in column D and copy/paste that into notepad? I would like the macro to run continuously so that by end of day my notepad has a list of all stocks that appeared as buys during the day. Could be tricky?

Not tricky at all, although you'd have to be careful that it doesn't slow down your computer too much. Do you know any VBA at all? Does the output need to be in notepad (it would be easier to send it to another worksheet and then if need be have it save to a .txt file as a separate module)? Where is the feed coming from? Assuming you know some VBA, have a play around with the Worksheet_Change event (nb this needs to be in the private module worksheet object).

You could probably write the script with 5 or 6 lines of VBA.

 

[Gringotts Bank]

Thanks pixel. I can get AB to do that already but I'm trying to bypass charting software and just use excel. My long term aim is to feed these buy signals automatically into my broker's web page as they happen. And most since most macro-style software seems to have difficulty reading AB's exploration results, excel might be the way to go, (being macro friendly and all). New territory for me.

Hi McLovin. Feed is from WebIRESS and I have no VBA knowledge. It doesn't have to go to notepad, it's just that if I can get it to notepad, then I know how to use another macro to get it from there to my broker (see paragraph above). I've found a function called VLOOKUP which is sort of the right thing except it only finds one instance of the BUY signal, and then only gives one column result, instead of three. It's possible I'm creating difficulties for myself.

All I really want to do is automate a system. AB's IBc looks ok but I have no idea how it works and so I won't open a TWS account just to find out it's unreliable (I've heard it is). I just want to identify the buy signals, then read the data from the related columns and form-fill to my broker. I thought it would be easy when I started!

 

[McLovin]

Hi McLovin. Feed is from WebIRESS and I have no VBA knowledge. It doesn't have to go to notepad, it's just that if I can get it to notepad, then I know how to use another macro to get it from there to my broker (see paragraph above). I've found a function called VLOOKUP which is sort of the right thing except it only finds one instance of the BUY signal, and then only gives one column result, instead of three. It's possible I'm creating difficulties for myself.

Ahh...Can you post an example of the data. I have a 5.5 hour and then 15 hour flight coming up in the next few days so writing the code will kill 5 minutes of boredom!

VLOOKUP is of little use. For what you are wanting to do you will have to go to VBA. Although with three VLOOKUP's and CONCATENATE you could create a .csv file but it will give you static data.

 

[Gringotts Bank]

I'm very pleased you can have a shot at this McLovin.

'BUY' has just been worked out to be true when certain columns meet certain requirements. That part of it is fine.

BHP, BUY, 35.24, -.58, -1.6, 1000,...
ANZ, NOACTION, 20.45, -.29, -1.4, 1000...
WOR, BUY, 26.28, -.14, -.5, 1000...

The critical column is B, in bold.

When 'BUY' appears in column B for any stock, then copy columns C and F from the same row. C is my buyprice and F is the amount of stock I want to buy.

So...
For BHP, Copy 35.24 and 1000
For WOR, copy 26.28 and 1000
ANZ gets ignored until or if 'BUY' appears in its second column
For every other stock... same process.
Immediately the data is copied, the entire row must be deleted to avoid re-submission to the broker. Could be disastrous otherwise!

Then with the copied data, automatically form fill it to my broker's order page and click submit. I can PM you my user name and password if you get to this stage. There's no money in this particular account.

Thanks for your offer.

 

[McLovin]

I'm very pleased you can have a shot at this McLovin.

'BUY' has just been worked out to be true when certain columns meet certain requirements. That part of it is fine.

BHP, BUY, 35.24, -.58, -1.6, 1000,...
ANZ, NOACTION, 20.45, -.29, -1.4, 1000...
WOR, BUY, 26.28, -.14, -.5, 1000...

Is column "B" present in the raw data or is it added (I'm assuming it is added per the previous posts). If it is added does it need to be there?

Then with the copied data, automatically form fill it to my broker's order page and click submit. I can PM you my user name and password if you get to this stage. There's no money in this particular account.

Thanks for your offer.

Unless you want to pay for my solicitor to draft a disclaimer then I won't be doing anything that involves orders. And anything I do give you should not be used to trade on etc etc...

I'm more interested in showing you how to use VBA than anything else. And I'm certainly not trying to build a trading program/system (or any components) for you.

 

[Gringotts Bank]

Is column "B" present in the raw data or is it added (I'm assuming it is added per the previous posts). If it is added does it need to be there?

Unless you want to pay for my solicitor to draft a disclaimer then I won't be doing anything that involves orders. And anything I do give you should not be used to trade on etc etc...

I'm more interested in showing you how to use VBA than anything else. And I'm certainly not trying to build a trading program/system (or any components) for you.

Yes B is added, and is calculated from the raw live data. Probably doesn't need to be there as a column in itself. To calculate it, I used that nested IF function [edited]

I was planning to offer payment if it looked like something you could automate for me. But whatever you can do I'd appreciate. I have no idea if it's a 5 minute or 5 hour job.