OpenOffice Calc Tips

I was intrigued to see that OOo Calc supported the creation of candestick charts.

What follows is not so much a tutorial on how to effectively make use of the feature in OOo Calc - but rather a summary of my experiences in trying to generate some meaningful charts from historical stock data available on the Web.

You are probably familiar with candlestick charts. One of the attractions is that in a single graphic, four useful stock metrics are presented, opening price, closing price, intraday hgh, and intraday low.

With no information or documentation to work with, I figured that in order to create a candlestick chart, I would need these four aforementioned metrics for my selected stock over a timeperiod that was of interest to me - say the last two months. This information is readily available online at sites like Yahoo - see below.

Step 1: From the Yahoo page below, I selected all columns of the historical prices table - except adjusted closing price - for Freescale (FSL) - about two months worth of data. Even though Volume is not required for the candlestick chart, I grabbed it anyways as it is a key metric in technical analysis of stocks.

Step 2: I pasted the table into Ooo Calc as shown below. I use OOo 2.0 on Win XP and this direct copy/paste manouever from browser to spreadsheet went without a hitch. On other systems, your mileage may vary. Ultimately, we would want some mechanism whereby the stocks would be updated automatically at the close of trading each day. This would certainly be possible with some macro programming.

Step 3: I noticed that the table was arranged with the most recent data at the top. This would map into a candlestick moving from left to right - which is incorrect. I selected the table and then Data - Sort to bring up the Sort dialog. I then chose to sort by Column B - Ascending. The imported and flipped data is shown below.

Step 4: Select all columns except the volume and open the chart wizard with Insert - Chart

First page: Both first row and first column selected as labels…

Second page: We select the Stock chart type…

Third page: The variant of the stock chart type is Stock Chart 2 - otherwise known as the candlestick chart. I also selected Y-axis grid lines for better legibility. A shortcoming of OOo Calc is the lack of control one has with the X-axis. More about that later.

Fourth page: I added a basic title anddisabled the legend and axis titles for the moment.

My first pass. What needs immediate attention is the X-axis - which is useless in it’s present state. Let us fix that…

The one way to ‘fix’ the x-axis is to create an alternate column in our table that will be converted more legibly in the final chart. With a bit of experimentation, I came up with the arrangement below. When regenerating the chart, ignore the column on the far left and use the manually created column to it’s right.

The result of my first attempt at a candlestick chart. This leaves a lot to be desired - but it is certainly promising. Certainly, we would like to automate this whole process by using macros. An automated daily update of the table and chart would also be very useful. Stay tuned!

This entry was posted on Monday, October 10th, 2005 at 8:47 pm and is filed under Charting. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.