Hello.
Please can I request a spreadsheet-friendly date format in the CSV export facility at Recently Closed Trades.
See what sorting on date column currently does at:
http://img43.imageshack.us/img43/5307/c2csvdateformatrecently.jpg
Would
<Year-4digits> <Month-2digits> <Day-2digits> <Time-4digits>
e.g. 201208031615 - 3rd August 2012 at quarter past 4 afternoon
give instant sort results, without getting into how spreadsheets hold their dates internally, especially as I’m in UK and 9/11 entered in my UK-spec Microsoft spreadsheet gives 9th November?
Just an idea.
Kind regards,
OptionsCentral
68780464
Link[LINKSYSTEM_68780464]
Is it possible it’s your version of Office? I’m using Office 2010 and don’t get that error when sorting your recently closed trades.
Uk Options - Somehow you have converted the dates from a value format to a text format. So when you sort the date as text, the following is in the correct order 7/19/2012 , 7/2/2012 , 7/20/2012 . But obviously that is not what you want.
The CSV file contains raw numbers in the date columns. In other words, the cell with the first date shown in your jpg is actually 41106.4014 which is the same as 7/16/2012 9:38. If you keep the date in a value format, then it will sort properly. You can also make the date and time look like you want by entering yyyymmddhhmm into Format Cells > Number > Custom > Type.
Let me know if you need a better explanation and I can post a few jpg’s.
A minor correction - the CSV file does not contain raw numbers in the date columns. The date format in the CSV file is m/d/yy hh:mm.
Here are two methods to import these dates as values into Excel. The first method is to open the CSV file in a text editor like NotePad. Then select, copy, and paste all of the data into an Excel worksheet. Then parse the data into columns but do not specify the dates as text values.
The easier method is simply to open the CSV file with Excel. Excel 2010 will automatically parse the data into columns and should format the dates as values.
OK, one last comment. Windows regional settings contain the default date format which is likely date/month/year for the UK instead of month/date/year for the US. This might be causing the dates to be imported in text format instead of value format.
If so, then the first method described above should be used to import the data because the parsing step allows you to identify the CSV date format as month/date/year.
It would be best if another European user could confirm the best method to import the CSV data from C2 into Excel. Sorry about taking three posts to get this far.
Hello,
In the past I had issues using C2 dates in excel for calculations. Since then, I have built a set of excel formulas that digest the C2-specific format. It currently works in all csv imports. I use it to count days in trade, weekly performance, etc. All my CSV downloads are working fine now, displaying in separate columns: dd/mm/yyyy, hh:mm, yyyy, mm, dd, and week number used for calculations and pivot tables. It handles the C2 single digits cases such as: d/mm/yyyy, d/m/yyyy and dd/m/yyyy. Not to forget the h:mm vs hh:mm. Send me a private message if you would like me to make a simplified version of this excel file where you copy/past the csv C2 date column.
From my point of view, the CSV import is major tool in C2, including data such as "DD $", which differentiates C2 from other sites that are rather useless in fact. I hope the CSV import stays as it is. Should the format change in the future, Matthew, please provide a CSV sample at least one or two weeks in advance so that CSV users can adapt. Thanks.
Best regards,
Julio
I really was completely unaware the date column format was causing so much trouble. I’m so very sorry. Thanks for bringing this to my attention. I can change the format without too much trouble.
Would it break anyone’s stuff if the format became:
YYYYMMDD HH:mm:ss
where HH is 24 hour time (Eastern USA)
?
This format could be more widely useful to the C2 community I believe. I can adapt.
For any given CSV export, would it apply for all data lines of the file? Or a CSV export could contain “old” and “new” data format accross the lines? Would it be applied also to older systems that are no longer active?
Before going live with a new format, could you please provide an actual CSV sample of what it will look like, so that we can adapt our excel files ahead of time?
When going live with a new format, preferably, could you start on a Monday please? This would allow us to do our weekend homework normally, and have the following week to test the new format.
Since you do not change formats often, I take advantage to ask: does your database keep track of the opposite value of “DD $”? This would be “highest in-trade profit $ non-capitalized”. It would be great if it could be included in the CSV data. I’ve been hopeful that this additional data would become available one day.
Thanks!
Matthew - In order for Excel to recognize the date as a value (not as a text), you need to include separators. Based on some quick testing, the following two formats should work ok:
yyyy/mm/dd hh:mm:ss or yyyy-mm-dd hh:mm:ss
If you do not include the separators, the imported dates will be in text format and cannot be used as-is in calculations.
Thanks, LR.
You are truly a “Ke-mo sah-bee”.
Yes, I understand what you are suggesting.
If I alter the column’s format so I tell it that this is not plain text but date and time then maybe it’ll work it out for itself - good idea.
Trying it now.
------
Later …
Yes - the Works spreadsheet I’m using is too primitive - presumably that’s why it’s free on every laptop ! 
To quickly experiment with your idea I went to Excel Online at SkyDrive and the success of your approach (with a beefier spreadsheet) can be seen at:
http://img94.imageshack.us/img94/5425/c2csvdateformatexcelsky.jpg
Thanks for the tip. Saves me time. Saves C2 from programming my request. Cool.
OptionsCentral.
Link:
[LINKSYSTEM_68780464]
Sorry - earlier link is wrong.
Here’s the online Excel spreadsheet at SkyDrive that auto-converts CSV dates, after my Works spreadsheet couldn’t do it.
http://img407.imageshack.us/img407/5425/c2csvdateformatexcelsky.jpg
Thanks to everybody who posted - interesting issues about how each of us interacts with our system’s webpages, in such varied ways.
Maybe we should compile something on
"this is how I do it"
from individual system designers and then distill it down to some kind of recommended best practice guide, so people who are doing things in an unnecessarily long-winded way can learn faster, more efficient ways of handling their downloaded data, and allow sharing of spreadsheet formulae, for instance.
Just an idea.
OptionsCentral
Link:
[LINKSYSTEM_68780464]
Uk Options - Glad it worked. But this should only be an interim solution.
Matthew - The date format in the CSV file should still be changed to year/month/date to avoid these type of problems and to be consistent with international standards.
Techie plays with dates on primitive Works spreadsheet.
I thought some might be interested to see this variety of responses to ostensibly simple date input to the Microsoft Works spreadsheet, and to see how non-US versions of Microsoft spreadsheets will only respond to dates in very particular formats.
http://img846.imageshack.us/img846/2986/c2dateformatgeekguide20.jpg
As you’ve already explained to me, Excel can handle this stuff spontaneously with none of these headaches, and so is the preferred choice for system creators and system followers.
For those like me, who don’t have Excel on their machine, there is a cut-down online incarnation at SkyDrive.
For full Excel functionality, especially automatic data-gathering from, say, Yahoo Finance webpages, Microsoft Office is available on free trial by download.
Link:
[LINKSYSTEM_68780464]
Share your spreadsheets at www.editgrid.com (or Zoho or ThinkFree).
If you want a place to post your example spreadsheets (with formulae) there’s a moribund (no longer actively supported) website at EditGrid.
I haven’t quite worked out how straightforward downloading is, and some spreadsheet posters set various things to private, so maybe some parts are not totally visible (e.g. maybe you can’t see the formulae on some spreadsheets there, if that permission has been set as “denied”).
Also, check out the dangers to your machine if you download macro-enabled spreadsheets to your own machine.
As an example, here’s the spreadsheet I did for the graphic posted earlier.
http://www.editgrid.com/user/lagoonboy/C2_Works_dates_examples_20120823_xls
(Having saved this version of it in the old Excel format (.xls), and having posted it to an online spreadsheet program (EditGrid), it may not show the shortcomings I mentioned when I used the .xlr version in Works on my machine).
Whoops! Not much of an advertisement - it looks nothing like the original spreadsheet. Oh well … … you get the broad idea.
Maybe something more actively supported might be more useful, especially if a few of you want to collaborate on building the ultimate spreadsheet (and also want to select who may collaborate, and who can simply view), so check out the incredibly powerful suite at Zoho and also at ThinkFree.
For non-web-based software, I think ThinkFree also offers equivalent Microsoft Office functionality for a lower price if you want something that lives on your machine, and for the free Office equivalent, there’s Open Source “Open Office”.
Link:
[LINKSYSTEM_68780464]
