topic Re: Presenting stock data in form of box plot in Discussions

Presenting stock data in form of box plot

lukasz — Sun, 11 Jun 2023 11:06:50 GMT

Hello Everybody,

the data describing stock price movements is created by open/close price and max/min price. Is there any way to create box plot (candlestick chart) in JMP having real body from open and close price and upper and lower shadow having values in max (high) and min (low) prices respectively, like in the picture attached or is there better way to do that? The traditional Box Plot element, however, shows outlier or quantile box plots and provides a compact view of a distribution of values. I attached an example data table in which several timestamps and price values (min, open, close and high) are provided. I would appreciate for hints and suggestions.

(source: Wikipedia)

Best regards

inkRe: Presenting stock data in form of box plot

txnelson — Fri, 16 Oct 2020 20:14:17 GMT

I think this is the best that JMP can do without scripting

But with a little bit of effort, one could easily write some JSL that would create the Candlestick plots.

Re: Presenting stock data in form of box plot

statman — Fri, 16 Oct 2020 20:33:24 GMT

Yes, as Jim points out, Graph Builder can do this easily. As you get more data, though, you might want to consider IR (individual moving range charts) as these will provide insight into consistency/stability of the metrics over time.

Re: Presenting stock data in form of box plot

txnelson — Sat, 17 Oct 2020 12:30:16 GMT

I had a little more time to look into this, and I think the easy solution below might be a workable solution for you.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Stock Prices.jmp" );

dt:open << label;
dt:close << label;

gb = dt << Graph Builder(
	Size( 1289, 456 ),
	Show Control Panel( 0 ),
	Variables( X( :Date ), Y( :High ), Y( :Low, Position( 1 ) ) ),
	Elements( Bar( X, Y( 1 ), Y( 2 ), Legend( 10 ), Bar Style( "Interval" ) ) ),
	SendToReport(
		Dispatch(
			{},
			"Date",
			ScaleBox,
			{ Interval( "Day" ), Inc( 1 ),
			Minor Ticks( 0 )}
		),
		Dispatch(
			{},
			"400",
			ScaleBox,
			{Legend Model(
				10,
				Properties( 0, {Line Width( 2 )}, Item ID( "High..Low", 1 ) )
			)}
		)
	)
);

// Add the boxes to the chart
Report( gb )[framebox( 1 )] << add graphics script(
	halfDayincr = In Days( 1 ) / 2 * .8;
	days = (Col Max( :Date ) - Col Min( :Date )) / In Days( 1 );
	Pen Color( black );
	Pen Size( 2 );
	For( i = 1, i <= N Rows( dt ), i++,
		If( :Open[i] <= :close[i],
			Fill Color( light green );
		,
			Fill Color( light red );
		);
		Rect( :date[i] - halfDayincr, Max( :open[i], :close[i] ), :date[i] + halfDayincr, Min( :open[i], :close[i] ), 1 );
		Rect( :date[i] - halfDayincr, Max( :open[i], :close[i] ), :date[i] + halfDayincr, Min( :open[i], :close[i] ), 0 );
	);
);

Re: Presenting stock data in form of box plot

lukasz — Sat, 17 Oct 2020 11:14:44 GMT

Thank you very much! I tried to use your code on the data from yahoo finance website, however, the data from there is saved as characters with "." decimal points which did not allow to convert values to correct numeric values. The below snippet converts values in columns and allows to plot them correctly. Thank you and best regards.

//open data
dt = open( "https://query1.finance.yahoo.com/v7/finance/download/URA?period1=1571303176&period2=1602925576&interval=1d&events=history&includeAdjustedClose=true" );

//change decimal point from "." to ",", otherwise later conversion to numeric format produces wrong results
nr = nrows(dt);
for (k = 1, k <= nr, k++,
	dt:Open[k] = substitute(dt:Open[k], ".", ",");  
	dt:Close[k] = substitute(dt:Close[k], ".", ","); 
	dt:High[k] = substitute(dt:High[k], ".", ","); 
	dt:Low[k] = substitute(dt:Low[k], ".", ","); 
	dt:name("Adj Close")[k] = substitute(dt:name("Adj Close")[k], ".", ","); 
);

//change format to numeric
dt:Open << Data Type( numeric ) << Modeling Type( continuous );
dt:Close << Data Type( numeric ) << Modeling Type( continuous );
dt:High << Data Type( numeric ) << Modeling Type( continuous );
dt:Low << Data Type( numeric ) << Modeling Type( continuous );
dt:name("Adj Close") << Data Type( numeric ) << Modeling Type( continuous );

Re: Presenting stock data in form of box plot

txnelson — Sat, 17 Oct 2020 12:36:22 GMT

What you found with the data returned from the Yahoo Finance data must be Country settings specific. The data that was returned to me in the U.S. did not have to be converted.

Re: Presenting stock data in form of box plot

lukasz — Sat, 17 Oct 2020 13:58:55 GMT

OK, thank you, I will notice that. Best regards

Re: Presenting stock data in form of box plot

ron_horne — Sun, 07 Nov 2021 11:15:30 GMT

Hi @lukasz

with respect to decimal points using "." or "," perhaps you can fix this from the preferences as in the picture below. Yet, i would assume that all other data sets that you are using will change for the wrong...

In this case, in order to just amend the format of a specific column in one file you can use the following command:

Format( x, "Currency", 20, <<Use Locale(0) );

Full description in this link:

https://www.jmp.com/support/help/zh-cn/15.2/index.shtml#page/jmp/formatting-numbers.shtml

let us know if it works.

ron

Re: Presenting stock data in form of box plot

lala — Mon, 06 Jun 2022 06:26:26 GMT

How can remove the gaps in the graph that are not trading days?
Thank Jim!

Re: Presenting stock data in form of box plot

jthi — Mon, 06 Jun 2022 08:27:31 GMT

You will most likely have to change X-axis to nominal and then possibly modify the graphics script to accommodate for that change.

Re: Presenting stock data in form of box plot

lala — Mon, 06 Jun 2022 08:49:23 GMT

Yes, the X-axis can display content other than the date.

But it is better to show the original date label when the mouse points over the graph.
That's what I was thinking.But I don't know how to change the code.

Thanks!

Re: Presenting stock data in form of box plot

lala — Mon, 06 Jun 2022 09:10:34 GMT

Re: Presenting stock data in form of box plot

txnelson — Sat, 11 Jun 2022 07:22:50 GMT

The columns that are displayed when you click on a data point, are columns that have the "Label" column property set. In my original code, the open and close columns have their Label values set. All you should have to do is to add a line for each of the columns you want to add the Label column property to.

Re: Presenting stock data in form of box plot

lala — Sun, 12 Jun 2022 14:11:35 GMT

I tried not to use date-spec data, but ended up not modifying JSL.It didn't work.
As in JSL below, how to draw box diagram with new data?Thank you very much!

dt = Open( "$SAMPLE_DATA/Stock Prices.jmp" );
dt << New Column( "tip", formula( Format( :Date, "yyyymmdd" ) ) );
dt << run formulas;
Column( "tip" ) << deleteFormula;
dt << delete columns( "Date" );
dt << New Column( "new", set each value( Row() ) );
dt:open << label;
dt:close << label;

gb = dt << Graph Builder(
	Size( 1289, 456 ),
	Show Control Panel( 0 ),
	Variables( X( :new ), Y( :High ), Y( :Low, Position( 1 ) ) ),
	Elements( Bar( X, Y( 1 ), Y( 2 ), Legend( 10 ), Bar Style( "Interval" ) ) ),
	SendToReport(
		Dispatch( {}, "new", ScaleBox, {Interval( "Day" ), Inc( 1 ), Minor Ticks( 0 )} ),
		Dispatch( {}, "400", ScaleBox, {Legend Model( 10, Properties( 0, {Line Width( 2 )}, Item ID( "High..Low", 1 ) ) )} )
	)
);

Report( gb )[framebox( 1 )] << add graphics script(
	halfDayincr = In Days( 1 ) / 2 * .8;
	days = (Col Max( :new ) - Col Min( :new )) / In Days( 1 );
	Pen Color( black );
	Pen Size( 2 );
	For( i = 1, i <= N Rows( dt ), i++,
		If( :Open[i] <= :close[i],
			Fill Color( light green ),
			Fill Color( light red )
		);
		Rect( :new[i] - halfDayincr, Max( :open[i], :close[i] ), :new[i] + halfDayincr, Min( :open[i], :close[i] ), 1 );
		Rect( :new[i] - halfDayincr, Max( :open[i], :close[i] ), :new[i] + halfDayincr, Min( :open[i], :close[i] ), 0 );
	);
);

Re: Presenting stock data in form of box plot

txnelson — Mon, 13 Jun 2022 03:57:55 GMT

When you changed from using column Date, which is a JMP date column(the number of seconds since January 1, 1904), to the column New, which is just an integer where each row is just 1 more than it's previous value, you needed to change the calculation of the variables, "days" and "halfDayincr". The number of days is just the number of rows, and the halfDayincr, which is half the width of the rectangle to draw, is simply .4.

gb = dt << Graph Builder(
	Size( 1289, 456 ),
	Show Control Panel( 0 ),
	Variables( X( :new ), Y( :High ), Y( :Low, Position( 1 ) ) ),
	Elements( Bar( X, Y( 1 ), Y( 2 ), Legend( 10 ), Bar Style( "Interval" ) ) ),
	SendToReport(
		Dispatch(
			{},
			"new",
			ScaleBox,
			{ Interval( "new" ), Inc( 1 ),
			Minor Ticks( 0 )}
		),
		Dispatch(
			{},
			"400",
			ScaleBox,
			{Legend Model(
				10,
				Properties( 0, {Line Width( 2 )}, Item ID( "High..Low", 1 ) )
			)}
		)
	)
);

// Add the boxes to the chart
Report( gb )[framebox( 1 )] << add graphics script(
	//halfDayincr = In Days( 1 ) / 2 * .8;
	//days = (Col Max( :Date ) - Col Min( :Date )) / In Days( 1 );
	halfDayincr = .4;
	days = Col Max( :new ) - Col Min( :new );
	Pen Color( black );
	Pen Size( 2 );
	For( i = 1, i <= N Rows( dt ), i++,
		If( :Open[i] <= :close[i],
			Fill Color( light green );
		,
			Fill Color( light red );
		);
		Rect( :new[i] - halfDayincr, Max( :open[i], :close[i] ), :new[i] + halfDayincr, Min( :open[i], :close[i] ), 1 );
		Rect( :new[i] - halfDayincr, Max( :open[i], :close[i] ), :new[i] + halfDayincr, Min( :open[i], :close[i] ), 0 );
	);
);

It is my strong suggestion that you start studying the code provided by the Community, so you are not limited to only being able to cut and paste it. Please take the time to read the Scripting Guide, and to also take the time to study the community provided code, until you understand how it works.

Re: Presenting stock data in form of box plot

lala — Mon, 13 Jun 2022 06:15:09 GMT

Thank Jim!

I want to strengthen the basic functions and grammar study.Now it's straight to the answer.

Re: Presenting stock data in form of box plot

lwx228 — Wed, 22 Jun 2022 01:52:49 GMT

Thanks again Jim for creating such an innovative image.

I tried to create the diagram manually myself, but still couldn't.

I'm going to take Jim's time again and ask if I can explain the process of making this diagram manually.Thank you very much!

I mainly want to add a smooth moving average to this graph.

In addition, can the black frame of each box diagram be removed?Because I've seen other posts that have boxes without black borders.

Thank Jim!

Re: Presenting stock data in form of box plot

txnelson — Wed, 22 Jun 2022 05:28:36 GMT

Here is a modification that handles the elimination of the black outline

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Stock Prices.jmp" );
dt << New Column( "tip", formula( Format( :Date, "yyyymmdd" ) ) );
dt << delete columns( "Date" );
dt << New Column( "new", set each value( Row() ) );
dt << New Column( "Direction", character, set each value( If( :Open <= :Close, "Increasing", "Decreasing" ) ) );
dt << run formulas;
Column( "tip" ) << deleteFormula;
dt:open << label;
dt:close << label;

gb = dt << Graph Builder(
	Size( 1289, 456 ),
	Show Control Panel( 0 ),
	Variables( X( :new ), Y( :High ), Y( :Low, Position( 1 ) ), Color( :Direction ) ),
	Elements( Bar( X, Y( 1 ), Y( 2 ), Legend( 10 ), Bar Style( "Interval" ) ) ),
	SendToReport(
		Dispatch( {}, "new", ScaleBox, {Interval( "new" ), Inc( 1 ), Minor Ticks( 0 )} ),
		Dispatch(
			{},
			"400",
			ScaleBox,
			{Legend Model(
				10,
				Properties( 0, {Fill Color( light red )}, Item ID( "Decreasing", 1 ) ),
				Properties( 1, {Fill Color( light green )}, Item ID( "Increasing", 1 ) ),
				Properties( 0, {Line Color( 19 ), Line Width( 2 )}, Item ID( "High..Low", 1 ) )
			)}
		)
	)
);

// Add the boxes to the chart
Report( gb )[framebox( 1 )] << add graphics script(
	//halfDayincr = In Days( 1 ) / 2 * .8;
	//days = (Col Max( :Date ) - Col Min( :Date )) / In Days( 1 );
	halfDayincr = .4;
	days = Col Max( :new ) - Col Min( :new );
	Pen Size( 1 );
	For( i = 1, i <= N Rows( dt ), i++,
		If( :Open[i] <= :close[i],
			Pen Color( light green );
			Fill Color( light green );
		,
			Pen Color( light red );
			Fill Color( light red );
		);
		Rect( :new[i] - halfDayincr, Max( :open[i], :close[i] ), :new[i] + halfDayincr, Min( :open[i], :close[i] ), 1 );
		Rect( :new[i] - halfDayincr, Max( :open[i], :close[i] ), :new[i] + halfDayincr, Min( :open[i], :close[i] ), 0 );
	);
);

Concerning how to manually/interactively

Drag the high and low columns to the Y axis drop area
Drag the new column to the X axis drop area
Right click on the chart and select Smoother=>Remove
Right click on the chart and select Points=>Change=>Bar
Right click on the chart and select Bar=>Bar Style=>Interval
Drag the Direction column to the Color drop area
In the legend, right click on the color box for Increasing and select Fill Color=>select a light green color
In the legend right click on the color box for decreasing and select Fill Color=>select a light red color

Now the boxes for each high and low need to be added. This is done by adding a graphic script to Graph Builder

Right click on the chart and select Customize
Click on the + box to add a new script

In the script editor area, add the following script (the script is developed by the individual user. In this case, I wrote the script)

halfDayincr = 0.4;
days = Col Max( :new ) - Col Min( :new );
Pen Size( 1 );
For( i = 1, i <= N Rows( dt ), i++,
	If( :Open[i] <= :Close[i],
		Pen Color( light green );
		Fill Color( light green );
	,
		Pen Color( light red );
		Fill Color( light red );
	);
	Rect(
		:new[i] - halfDayincr,
		Max( :Open[i], :Close[i] ),
		:new[i] + halfDayincr,
		Min( :Open[i], :Close[i] ),
		1
	);
	Rect(
		:new[i] - halfDayincr,
		Max( :Open[i], :Close[i] ),
		:new[i] + halfDayincr,
		Min( :Open[i], :Close[i] ),
		0
	);
);

Click on OK

Re: Presenting stock data in form of box plot

lwx228 — Wed, 22 Jun 2022 07:18:36 GMT

Thanks Jim for your patient guidance.Your code is great!
I learned it, too.Thank you very much!