View Single Post
  #1   Report Post  
silver23
 
Posts: n/a
Default How do we generate Candlestick chart using Microsoft Excel?

A few formulas help "spark" candles of interest.

For example, what is a Doji day?

We know what one looks like when we see it. But how to code for it?

Technical analysis literature lacks specificity about this, maybe owing to
its being art as well as "technical"; and suggests a Doji is when open and
close equal (or are near equal). Here's one attempt to define perfect AND
"near equal" Dojis.

This example uses OEF data. But the formula can be applied to any stock,
index, option, commodity or currency futures contract. Basically, anything
that's traded. I also tested it using the newest $INDU-based CBOE implied
volatility index, $VXD.

Two adjustable variables address end-user preferences. A "near equal" Doji
for $1100 $SPX differs from $24 MSFT; and is obviously subjective.

$G$2 = 0.001 (compared to underlying price, it represents one-tenth of
one-percent; and is meant to catch the smallest of dojis; in the example, a
nickel or less on $50 OEF is a Doji)

$G$3 = 0.150 (a real body 15% or less of today's total candle size is a
Doji; an OEF $1 candle allows for a fifteen-cent Doji)

sample data (DOHLCV):

Date Open High Low Close Volume Doji?
08/26/04 53.95 54.02 53.86 53.90 122388 DOJI

formula:
=IF(OR(ABS($E5-$B5)<$E5*$G$2,ABS($E5-$B5)<(ABS($C5-$D5)*$G$3)),"DOJI","")

Next step, is it a Harami Cross? Need the prior day:

08/25/04 53.60 54.00 53.38 53.95 86285
08/26/04 53.95 54.02 53.86 53.90 122388 DOJI HARAMI CROSS

=IF(AND($G5="DOJI",MIN($B4,$E4)<=$B5,MAX($B4,$E4) =$B5,MIN($B4,$E4)<=$E5,MAX($B4,$E4)=$E5),"HARAMI CROSS","")

Is the Harami Cross Bullish or Bearish?

08/25/04 53.60 54.00 53.38 53.95 86285
08/26/04 53.95 54.02 53.86 53.90 122388 DOJI HARAMI CROSS BEARISH

=IF($H5<"",IF($B4$E4,"BULLISH","BEARISH"),"")

02/09/05 57.73 57.73 57.16 57.16 286700
02/10/05 57.39 57.49 57.20 57.42 95100 DOJI HARAMI CROSS BULLISH

A different single-cell formula offers-up DOJI STARs:

MM/DD/YY 57.01 57.73 57.01 57.65 286700
MM/DD/YY 57.89 57.99 57.79 57.89 95100 DOJI BEARISH DOJI STAR

MM/DD/YY 59.01 59.01 58.21 58.21 286700
MM/DD/YY 57.89 57.99 57.79 57.89 95100 DOJI BULLISH DOJI STAR

=IF($G11="DOJI",IF(OR($D11$C10,$C11<$D10),IF($C11 <$D10,"BULLISH DOJI
STAR","BEARISH DOJI STAR"),""),"")

"cychay" wrote:

Hi everyone. I am a trader, i trade in the stockmarket as well as the FUTURES
market. Candlestick chart is a japanese chart used by many traders for them
to assess the market condition. How do we generate Candlestick chart using
Microsoft Excel ? Are there any available patch/upgrades out there for me to
download? Your help is very much appreciated, thank you.