Remember Me?

#1
April 30th 05, 11:32 PM
 silver23 Posts: n/a
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?

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.

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Bob Charts and Charting in Excel 5 May 1st 05 02:06 AM ckrogers Charts and Charting in Excel 3 March 17th 05 09:20 PM Alfred S C Lee Charts and Charting in Excel 2 December 29th 04 05:54 PM Phil Hageman Charts and Charting in Excel 4 December 17th 04 07:25 PM souris Charts and Charting in Excel 2 December 7th 04 03:56 AM

All times are GMT +1. The time now is 10:57 PM.