Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 30th 05, 11:32 PM
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Scrollbar on Chart Jumps to Left when Chart is Clicked Bob Charts and Charting in Excel 5 May 1st 05 02:06 AM
Timeline Chart? ckrogers Charts and Charting in Excel 3 March 17th 05 09:20 PM
Problem with xlusrgal.xls file Alfred S C Lee Charts and Charting in Excel 2 December 29th 04 05:54 PM
Impedding/Overlaying Charts Phil Hageman Charts and Charting in Excel 4 December 17th 04 07:25 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 03:56 AM


All times are GMT +1. The time now is 05:19 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017