Change the Number Format in an imported Catagory

3.15K viewsDataLink
0

Dear all,

I have copy & pasted a table of data from an csv file opened in Excel to via the Tools -> Data Link -> Import from Clipboard.

Data source is Google Finance:
[url:2ztuzsv7]http://finance.google.com/finance/historical?cid=24599&startdate=Jan+1%2C+2007&enddate=Apr+28%2C+2008[/url:2ztuzsv7]

I changed the data format for the date column to ‘Date’ and the Data Link Wizard shows the correct format of the date colunn. Then I choose ‘Two dimensional Matrix’ and check ‘use first column as row names’. Then I click ‘Finish’.

The data is imported but the dates are either in the format ‘39552’ or ’31-Mar-08′. The latter one is ok the first one is not. How can I change the formating of the dates even it is a category?

I have the attached the necessary files to reproduce these steps.
Quantrix Version: 3.0.

Many thanks for your help!

Regards,
Dominik

0

Mike,

Thank you very much for your help!
Your hint is very useful.

Dominik

0

Hi Dominik – Thanks for posting:

The 2D import is limited in its formatting capabilities. I agree it seems like it should be using the formatted date in this case. I will log for investigation.

There is a pretty good workaround however. Instead of choosing a 2D import – choose a Multi-d Import. Called the Date column a category, and then you will be presented with a screen to format the date to your liking. It will produce exactly the same format for the matrix you are looking for.

Feel free to post if you have any additional questions.

-Mike