Data Sources

Raw Data Formats
Data Types
Loading & Defining Data
Data Updating & Caching
Searching for Data

Raw Data Formats

ChartMechanic can import data from several sources:

• Microsoft Excel (XLS) files
• Google Docs spreadsheets
• Comma or space-separated value (CSV) data
• HTML tables
• Statistical Data and Metadata eXchange (SDMX)

ChartMechanic reads all of these kinds of "raw" data formats as tabular; that is, as having rows and columns. Each column in a data source has a data type. All cells in a particular column are expected to conform to that column's data type. When importing most tabular data sets, ChartMechanic will automatically detect the valid rows and columns of data, the data type of each column, and a logical name for each column, if available.

For example, when importing a spreadsheet or other raw data arranaged like this:

	column 1	column 2	column 3
row 0	Sales figures by month
row 1	and Representative
row 2	Month	Sales	Representative
row 3	May 2008	$500	Larry
row 4	June 2008	$800	Moe
row 5	July 2008	$1,300	Curly
row 6	August 2008	$900	Shemp
row 7	September 2008	$2,300	Joe

ChartMechanic will detect that:

1. The data begins at row 3, the first row with dates and dollar values
2. The columns are named Month, Sales and Rep
3. The columns have types DATE, NUMBER and TEXT, respectively

For raw data sets organized in a regular way, like the example above, the automatic detection of the valid range, and of column names and types, will usually suffice for the way you want the data set represented in ChartMechanic. If the raw data set has a more complex, irregular layout, the automatic data detection might not be sufficient. In that case, you can still define the attributes of your data set manually: which columns are DATE, NUMBER or TEXT types, valid data ranges, and column names. For details on manually defining data types and ranges, see Defining Data.

Some common data formats, like accounting or financial reports, may have linked data extended across a row instead of down a column. For example, a data set might be laid out like this:

Quarterly New Customer Registrations
Quarter	2007Q2	2007Q3	2007Q4	2008Q1	2008Q2	2008Q3	2008Q4	2009Q1
Registrations	1	3	5	7	5	10	30	48

Swap (transpose / invert) rows & columns

With DATE values on a single row, labeled Quarter, and associated NUMBER values on the following row, labeled Registrations. ChartMechanic will import data like this by inverting, or swapping, the rows and columns. After inverting the data, it will effectively look like this to ChartMechanic, with DATE and NUMBER values grouped into columns instead of rows:

Quarter	Registrations
2007Q2	1
2007Q3	3
2007Q4	5
2008Q1	7
2008Q2	5
2008Q3	10
2008Q4	30
2009Q1	48

The process of importing and defining data allow for the raw data format to be inverted in this way as appropriate; see for details.

To summarize: a data source is a raw, tabular data format, with rows and columns, where each column has a name and a type. Optionally, a data source may also define a range of valid data, namely the rows of the raw format where the valid data begins and ends.

Data Types

ChartMechanic uses 3 basic types for all data values: DATE, NUMBER or TEXT. All data cells within a data source table have one of these three types, as determined by the type of the cell's column. The data type of each column of a data source is determined, either automatically or manually, when the data is imported & defined. Expanding on our previous data set example, we would want the column types defined as follows:

Month (DATE)	Sales (NUMBER)	Representative (TEXT)
May 2008	$500	Larry
June 2008	$800	Moe
July 2008	$1,300	Curly
August 2008	$900	Shemp
September 2008	$2,300	Joe

Any data values may be considered as TEXT by ChartMechanic. ChartMechanic tries to be lenient and pragmatic in parsing data. However, not all values from your source data can always be understood as NUMBER or DATE values. When a NUMBER or DATE cannot be successfully parsed as such by ChartMechanic, that cell is treated as if it had been empty; that is, there is no value for that cell.

Supported Number Formats
Currency symbols, exponential notation, commas, and decimal points are valid as part of a number. Extraneous alphabetical text, such as units, labels or other markers, invalidate a cell's consideration as a number:

Example	Valid Number?
12,345	yes
1.23E3	yes
$12,345	yes
€12,345.00	yes
¥12,345	yes
12345 dollars	no
12345 (see footnote)	no

Supported Date Formats
The current list of supported date formats is shown below. The most common ways of expressing dates are included on this list, and new date formats are added all the time to ChartMechanic.

Format	Example	Accepts time format?
MMM d, yyyy	Dec 5, 2006	yes
MM/dd/yy	12/05/06	yes
MM-dd-yy	12-05-06	yes
yyyy-MM-dd	2006-12-05	yes
yyyyMMdd	20061205	yes
yyyy	2006	no
yyyy.'0'	2006.0	no
'FY' yy	FY 06	no
dd-MMM-yy	05-Dec-06	yes
dd MMM yy	05 Dec 06	yes
dd-MMM	05-Dec	no
EE MMM dd yy	Tue Dec 05 06	yes
EE MMM dd HH:mm:ss yy	Tue Dec 05 20:16:45 06	no
MMM yy	Dec 06	no
MMM-yy	Dec-06	no
MMM yyyy	Dec 2006	no
MMM-yyyy	Dec-2006	no
MM/yy	12/06	no
MM/yyyy	12/2006	no
EE MMM dd HH:mm:ss z yyyy	Tue Dec 05 20:16:45 UTC 2006	yes
dd MMM	05 Dec	yes
yyyy/MM	2006/12	no
yyyy-MM	2006-12	no
yyyy-MMM	2006-Dec	no
yyyy MMM	2006 Dec	no
yyyy-MMM'.'	2006-Dec.	no
yyyy-MMM'.' d	2006-Dec. 5	no
yyyy.MM	2006.12	no
yyyy-MMM d	2006-Dec 5	no
dd MMM yyyy	05 Dec 2006	no
yyyy-MM-dd'T'HH:mm:ss.SSS	2006-12-05T20:16:45.680	no
yyyy-MM-dd'T'HH:mm:ss.SSSZ	2006-12-05T20:16:45.680+0000	no
yyyy-MM-dd'T'HH:mm:ss.SSS'Z'	2006-12-05T20:16:45.680Z	no
quarter	2006-Q4	no
MMM. dd, yyyy	Dec. 05, 2006	no
yyyyMMM	2006Dec	no
yyyy'M'MM	2006M12	no
dd/MM/yy	05/12/06	no
HH:mm:ss	20:16:45	no

Loading & Defining Data

Raw Data from the Web

You can load data into ChartMechanic from anywhere on the web. Using the New Chart or New Data wizard of the Chart Editor, enter a URL to a supported data format in the Data URL field. For example, the example below is loading a Microsoft Excel spreadsheet of U.S. GDP data from the U.S. Bureau of Economic Analysis:

(The example spreadsheet URL is here - try it out!)

If the URL to the data is password-protected, you will be prompted for a user name & password.

Raw Data from ChartMechanic VFS

You may also access raw data files (Microsoft XLS, or CSV files) that have been uploaded into ChartMechanic VFS. Use the browse button ... next to the Data URL box to look for data files in your VFS folders, as shown below:

Once ChartMechanic can connect to the data you want, either from the web or from VFS, you'll see a screen showing the rows and columns of the data set. This is where you can define:

• Each column's name
• The data type of each column DATE, NUMBER or TEXT
• The row to use for Column Names
• The row where the valid part of the data begins
• Optionally, the row where the valid part of the data ends

In the spreadsheet example above, the last row labelled TOTAL is the SUM() of the numbers in the previous rows. If you do not want such a sum to appear in your charts of these data, you would select Data Ends in the left-most box for the previous row.

The top row with a blue background shows the name and data type (in parentheses) of each column. You can change the name and data type of a column by clicking on the edit icon in the top row. If a column contains data that you do not need, or if you want a particular column excluded from the data that you're loading into ChartMechanic, you can edit that column and select IGNORE as its data type.

Once you are satisfied with the settings on the data definition screen, you can proceed to the next step to save your data source in ChartMechanic.

Data Updating & Caching

ChartMechanic uses a cache, or local copy, of the data it reads off the web for you. Using cached data copy greatly improves the speed with which ChartMechanic can render charts. It also ensures that data will remain available to ChartMechanic, even when a remote website is down or unavailable. Periodically, when the cached data is old enough, it is considered "stale" and the data cache will be refreshed.

You can control how frequently ChartMechanic refreshes your data. In the Valid For field of your data source, select an option from the list:

• one hour
• one day
• one week
• one month
• always

Searching for Data

ChartMechanic maintains a search database of all the data sources it has imported. The search database is keyword-based, using both the description of the data source, and its name in VFS. You can enter appropriate keywords for your data source in it's Description field. Over time, as you add useful data to your library on ChartMechanic, it's a good idea to keep good keywords attached to you data, so that you (and other users) can find it again later.