Microsoft Excel Q&A

[skc]

Hello excel gurus. if I have a data series plotted on a bar graph such as this,

='P & L'!$B$83:$B$120,'P & L'!$D$83:$D$120

Is there a way to exclude one cell, being D103?

Under Charts / Select Data, edit the reference cells to something like.


='P & L'!$B$83:$B$102,'P & L'!$B$104:$B$120, 'P & L'!$D$83:$D$102,'P & L'!$D$104:$D$120

 

[Trembling Hand]

Thanks guys, that did it. I should think about these things while I'm not trading

 

[skc]

Thanks guys, that did it. I should think about these things while I'm not trading

You shouldn't not chart a data point just because it was a bad week

(Or may be it was holiday - in which case just delete the data row).

 

[Trembling Hand]

You shouldn't not chart a data point just because it was a bad week

haha. Only hiding it from the forum because I posted my results on that day. With that you would be able to figure out the scale. Then someone would reverse engineer a bot to rob me of my $50 a day....

 

[Kryzz]

Anyone know how to work out the average number of days, so long as a third cell meets a given criterion? This has been driving me crazy!

I.e, I want to know the average length of time winning trades held, easy enough to figure out the average days of ALL trades, all I want to know is the average of the winning trades only, i.e. average hold time so long as column K is positive (see attached)....any thoughts? (also, could this all be done in one cell?)

Thanks in advance!

 

[skc]

Anyone know how to work out the average number of days, so long as a third cell meets a given criterion? This has been driving me crazy!

I.e, I want to know the average length of time winning trades held, easy enough to figure out the average days of ALL trades, all I want to know is the average of the winning trades only, i.e. average hold time so long as column K is positive (see attached)....any thoughts? (also, could this all be done in one cell?)

Thanks in advance!

=sumif(K2:k100,">0")/countif(k2:k100,">0")

To do it for losing trade, replace ">0" with "<0"

Alternately, you can add a column testing whether the trade is a win or a loss. Something like =If(k2>0,"Win","Loss"). The benefit of this is you can use this column in other analysis (e.g. a pivot table).

 

[Kryzz]

=sumif(K2:k100,">0")/countif(k2:k100,">0")

To do it for losing trade, replace ">0" with "<0"

Alternately, you can add a column testing whether the trade is a win or a loss. Something like =If(k2>0,"Win","Loss"). The benefit of this is you can use this column in other analysis (e.g. a pivot table).

Thanks SKC, I was particularly after the time held however, i.e. in the example I posted, the average time a winning trade held was 26 days, the average losing trade was 11 days, but couldn't work out how to work this into a formula, I would have thought, the "averageif" function would have been the one, no luck however.

I hear a lot of good things about pivot tables, must learn more about them*

 

[pixel]

Anyone know how to work out the average number of days, so long as a third cell meets a given criterion? This has been driving me crazy!

I.e, I want to know the average length of time winning trades held, easy enough to figure out the average days of ALL trades, all I want to know is the average of the winning trades only, i.e. average hold time so long as column K is positive (see attached)....any thoughts? (also, could this all be done in one cell?)

Thanks in advance!

I would fill column M with number of days, i.e. type in M2 the formula =L2-A2, highlight M2 down to M5 and use Ctrl-D to fill it down.
Then use the array formula at the bottom of column M - in your example in M6 - typing
=AVERAGE(IF(K$2:K5>=0,M$2:M5))
and, instead of using the Enter key, use Shift - Ctrl - Enter.

 

[IB12]

if k2>0, datedif "d" divide by days, 0

 

[rnr]

Anyone know how to work out the average number of days, so long as a third cell meets a given criterion? This has been driving me crazy!

I.e, I want to know the average length of time winning trades held, easy enough to figure out the average days of ALL trades, all I want to know is the average of the winning trades only, i.e. average hold time so long as column K is positive (see attached)....any thoughts? (also, could this all be done in one cell?)

Thanks in advance!

ColM:Row2
=IF(K2>0,L2-A2,0)

Copy above to ColM Rows 3 to 5

ColM:Row6
=SUM(M2:M5)

ColK:Row6
=COUNTIF(K2:K5,">0")

ColM:Row7
=M6/K6

 

[Trembling Hand]

Dudes! What formula do I use to transfer this Date into Text like this,

20-Jul-12 to 20120720
23-Jul-12 to 20120723
24-Jul-12 to 20120724

 

[zuk]

Dudes! What formula do I use to transfer this Date into Text like this,

20-Jul-12 to 20120720
23-Jul-12 to 20120723
24-Jul-12 to 20120724

You don't need a formula specifically. Format the cell, go to "Number" tab, then select "Custom". In the "Type" fields, add the following text:

yyyymmdd

 

[skyQuake]

Dudes! What formula do I use to transfer this Date into Text like this,

20-Jul-12 to 20120720
23-Jul-12 to 20120723
24-Jul-12 to 20120724

format cells>category:custom

yyyymmdd

edit: looks like zuk beat me to it

 

[Trembling Hand]

Thanks guys thats what I needed

 

[hotchy]

I'm using the asx website to get 20 minute delayed data to a separate sheet in my excel file, but when there is an announcement, the ticker has a "*" sign after it - "BHP *" instead of "BHP"

This causes issues with my vlookup formula. So I'm looking for one of two solutions:

A data feed that doesn't include the "*" on the ticker (but must be able to search for multiple tickers at the same time)
or
Can someone fix my formula?

Thanks!

 

[skyQuake]

I'm using the asx website to get 20 minute delayed data to a separate sheet in my excel file, but when there is an announcement, the ticker has a "*" sign after it - "BHP *" instead of "BHP"

This causes issues with my vlookup formula. So I'm looking for one of two solutions:

A data feed that doesn't include the "*" on the ticker (but must be able to search for multiple tickers at the same time)
or
Can someone fix my formula?

Thanks!

depends how you're pulling the data from the ASX...

but this may be a solution,

assume A1= BHP or BHP*

=vlookup(left(A1,3),blah,blah,TRUE)

 

[skc]

I'm using the asx website to get 20 minute delayed data to a separate sheet in my excel file, but when there is an announcement, the ticker has a "*" sign after it - "BHP *" instead of "BHP"

This causes issues with my vlookup formula. So I'm looking for one of two solutions:

A data feed that doesn't include the "*" on the ticker (but must be able to search for multiple tickers at the same time)
or
Can someone fix my formula?

Thanks!

You can pull delayed data from Yahoo or Google Finance. Not sure about the multiple ticket thing though, but surely there's a way around that.

If you post your formula I am sure someone would be able to fix it.

 

[hotchy]

You can pull delayed data from Yahoo or Google Finance. Not sure about the multiple ticket thing though, but surely there's a way around that.

I'm using external data, searching from CELL A1 in a seperate sheet, with a custom formula (module?) that displays all the tickers in my other sheet. I followed this tutorial: http://www.get-digital-help.com/2010/01/30/stock-alerts-in-excel/

I had to adjust the module slightly as to not add comma's between the stock codes, which i worked out after some trial and error.

If you post your formula I am sure someone would be able to fix it.

=VLOOKUP(B3, Data!$A$49:$B$52, 2, FALSE)

Tried
=VLOOKUP(LEFT(B3,3), Data!$A$49:$B$52, 2, FALSE)

But it isn't working If i use "TRUE", it messes up the stock codes!

p.s. I'm a novice at excel! Probably going about this the wrong way!

EDIT: Tickers had to be in alphabetical order for this to work. I think i can work around this

 

[pixel]

I'm using external data, searching from CELL A1 in a seperate sheet, with a custom formula (module?) that displays all the tickers in my other sheet. I followed this tutorial: http://www.get-digital-help.com/2010/01/30/stock-alerts-in-excel/

I had to adjust the module slightly as to not add comma's between the stock codes, which i worked out after some trial and error.

=VLOOKUP(B3, Data!$A$49:$B$52, 2, FALSE)

Tried
=VLOOKUP(LEFT(B3,3), Data!$A$49:$B$52, 2, FALSE)

But it isn't working If i use "TRUE", it messes up the stock codes!

p.s. I'm a novice at excel! Probably going about this the wrong way!

EDIT: Tickers had to be in alphabetical order for this to work. I think i can work around this

Can you post the data sheet?
Specifically: What is in $A$49:$B$52? And what is in B3?

After running a few checks, I presume that your data sheet has the ASX data loaded with the codes in column A, the last price in column B.
If that is correct, you won't be able to lookup "BHP" and expect to find it in any table where the key in column 1 is "BHP***". That's because the LEFT(B3) applies to BHP, not the table value. You'd need an array formula over the Data sheet.

In order to overcome the problem, you can do one of two things:
Create a new table in an LookupData spreadsheet, which you fill with =LEFT(Data!$A$49,3) etc and =VALUE(Data!$B$49) etc, then use =VLOOKUP(B3, LookupData!$A$49:$B$52, 2)
Or you'll use a combination of FIND and INDEX. But that's more convoluted, so I won't suggest it if you're a novice.

 

[skyQuake]

Can you post the data sheet?
Specifically: What is in $A$49:$B$52? And what is in B3?

After running a few checks, I presume that your data sheet has the ASX data loaded with the codes in column A, the last price in column B.
If that is correct, you won't be able to lookup "BHP" and expect to find it in any table where the key in column 1 is "BHP***". That's because the LEFT(B3) applies to BHP, not the table value. You'd need an array formula over the Data sheet.

In order to overcome the problem, you can do one of two things:
Create a new table in an LookupData spreadsheet, which you fill with =LEFT(Data!$A$49,3) etc and =VALUE(Data!$B$49) etc, then use =VLOOKUP(B3, LookupData!$A$49:$B$52, 2)
Or you'll use a combination of FIND and INDEX. But that's more convoluted, so I won't suggest it if you're a novice.

^this