Microsoft Excel Q&A

skc · 3 September 2012

Trembling Hand said:
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 · 3 September 2012

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

skc · 3 September 2012

Trembling Hand said:
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 · 3 September 2012

skc said:
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 · 14 January 2013

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 · 14 January 2013

Kryzz said:
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 · 14 January 2013

skc said:
=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 · 14 January 2013

Kryzz said:
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 · 14 January 2013

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

rnr · 15 January 2013

Kryzz said:
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 · 29 January 2013

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 · 29 January 2013

Trembling Hand said:
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 · 29 January 2013

Trembling Hand said:
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 · 29 January 2013

Thanks guys thats what I needed

hotchy · 1 February 2013

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 · 1 February 2013

hotchy said:
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 · 1 February 2013

hotchy said:
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 · 2 February 2013

skc said:
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.

skc said:
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 · 2 February 2013

hotchy said:
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 · 2 February 2013

pixel said:
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

Microsoft Excel Q&A

skc

Goldmember

Trembling Hand

Can be found on the bid

skc

Goldmember

Trembling Hand

Can be found on the bid

Kryzz

shaun

Attachments

skc

Goldmember

Kryzz

shaun

pixel

DIY Trader

IB12

rnr

Trembling Hand

Can be found on the bid

zuk

skyQuake

Trembling Hand

Can be found on the bid

hotchy

skyQuake

skc

Goldmember

hotchy

pixel

DIY Trader

skyQuake

Similar threads

Microsoft Excel Q&A

Goldmember

Can be found on the bid

Goldmember

Can be found on the bid

shaun

Attachments

Goldmember

shaun

DIY Trader

Can be found on the bid

Can be found on the bid

Goldmember

DIY Trader

Similar threads

Privacy & Transparency

Privacy & Transparency