OpenOffice Calc Tips

By default, OOo Calc will create a chart with the y-axis starting at 0 (zero). For most purposes, this is adequate, but it is quite straightforward to shift the axis up (or down) where desired.

For the Flash version of this tip, click here.

Consider the data set below, which we wish to chart. This has been covered before elsewhere.

The default chart. In this example, we will adjust the y-axis to start at 20.

We first select the chart for editing by double-left-clicking anywhere inside the chart area. A solid border should appear around the chart as shown below.

Now enter the y-axis editor with the right mouse button as shown below.

Make sure the scale tab is selected. Before we modify the axis scaling, we first deselect the automatic settings. The fields indicated by the red arrows need to be changed.

When you have completed the desired modifications to the y-axis settings, close the dialog and deselect the chart. The final modifed chart is shown below.

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!

If you are familiar with project management and the various tools that are used to schedule deadlines and resources, you may be familiar with Gantt charts.

In this particular tutorial, we look at using stacked bar charts

First, we need to tabulate our project data. The numbers in columns D & E are total days completed & estimated days remaining in each task respectively.

Selecting the above table, we invoke the chart wizard with Insert - Chart.

Both the first row and column are selected as labels..

Select the Bar Chart type - below…

Of the different bar chart types, we choose the stacked variant - below…

Here, we add a meaningful title..

The ‘finished’ chart. We have some postprocessing further to perform before it looks like a Gantt chart. Select it for editing…

The blue bar above represents the start date of each task. We will make it disappear. Fist double click on any blue bar to invoke the Data Series editor below. Set the area fill to None and remove the border.

This is what the chart looks like with the blue bars removed …

Now select the Y axis for editing as shown below…

We now want to adjust the Scale settings for the Y-axis based on the timelines defined in the initial table.

The major interval (61.0) is approximately 2 months. The minor interval (1.0) is one day.

On the Label tab, we rotate the text to almost 270 degrees as shown below…

The finished Gantt chart…

In this short tutorial, we will change the attributes of the chart title and the chart legend.

(more…)

In this tip, we see how to modify the attributes of the axes on an already created chart.

(more…)

A step by step - and hopefully easy-to-follow - guide to creating a basic pie chart.

(more…)

A picture tells a thousand words. In this introduction to OOo Calc charting capabilities, we will create a simple chart as an illustration of the operation of the charting AutoPilot.

(more…)