|
PureBytes Links
Trading Reference Links
|
In preliminary testing, step(3)&(4) can be deleted ie. the file is straightaway saved as *.csv.
step(5) -> step(3) save file straightaway as csv (comma delimited) (*.csv).
In addition, Step (8): copy and paste from csv file to excel file.
(If excel file involves cells of multiple fonts size, types, formats, etc .... most probably, copy, right-click, paste special -> values and number formats).
After going through exercise, one could have initial insight of how excel read lower level data files (like *.txt, *.csv), depending on current setting of windows. Moreover, data it doesn't understand are earmarked textstring - for eg, in a US setting (m/d/yyyy), 23/2/2006 is passed off as textstring while 12/2/2006 is Dec 2, 2006.
I won't be surprised if there is a function(s) to do all these hidden inside excel.
Regards,
----- Original Message -----
From: ckng001@xxxxxxxxxxxx
To: equismetastock@xxxxxxxxxxxxxxx
Sent: Monday, March 20, 2006 12:37 PM
Subject: One solution to change dates key-in wrongly in excel files
To re-cap,
Problem: x/y/zzzz date of UK format (d/m/yyyy), key-in in a default US (m/d/yyyy) Excel cells.
Solution: Involves saving to text&csv files and then changing the regional&language options.
1) save a backup copy of the excel file.
2) copy the relevant dates columns onto a blank excel file.
3) save file as text (tab delimited) ( *.txt).
Editing the text file - add relevant commas
4) Open text file and replace all tabs with commas (no fear when dealing with multiple empty cells in multiple columns).
5) save text file as *.csv ie. when file->save as -> replace .txt with .csv.
6) change the regional option in control panel to the desired date format (UK, d/m/yyyy).
7) open the excel file which need corrections and the *.csv file created.
8) copy and paste from csv file to excel file (most probably, copy, right-click, paste special->values).
If additional excel files need changing, repeat the process but first revert to original regional setting ie. US format. Not so doing would result in erroreous correction of dates of 13th onwards eg. 1/13/2006, 2/15/2005.
I also revert back to the US regional setting after I'm done with all changes - uniformity in my current excel files and with future files including imported ones (as the default setting of Microsoft softwares, Metastock is US) and so as to avoid unexpected errors, compatibility issues, etc. (for eg., dates cut&paste normally from a US cells to a UK cells would be displayed in the US format though they are of the same value). New date inputs from here-on will be key-in as per US date format.
Any improvements, comments are welcomed. Thank you.
Best regards,
ckng
----- Original Message ----- >
> Message: 9
> Date: Fri, 17 Mar 2006 11:22:26 +0800
> From: <ckng001@xxxxxxxxxxxx>
> Subject: Re: Changing existing date format (Excel files)
>
> Dear Ron,
>
> Thank you for reply. The excel files aren't obtained by conversion with
> downloader. They are key-in manually for reference purpose, eg. warrants
> info with their respective expiry date and conversion price. By default
> when dates are key-in manually, it's interpreted along the US date format by
> excel (since the default regional setting in control panel is just that).
> They will be wrong if they are key-in as d/m/yyyy in a default m/dd/yyyy
> setting, in the first place.
>
> (The downloader (ver 8.0) could export (convert) files to excel - the dates
> come in mm/dd/yyyy (general format) irrespective of the regional setting.
> To adjust the excel data, for eg, sort in descending order - the date
> columns is first reformatted to date (from general)).
>
> Best regards,
>
> >
> > Message: 8
> > Date: Thu, 16 Mar 2006 08:40:05 -0500
> > From: "Ron Berlin" <rsb_44@xxxxxxxxxxx>
> > Subject: RE: Re: Changing existing date format (Excel files)
> >
> > Are you certain what appears as dates in excel are in fact numeric values
> > that can be formatted as dates, or are they text strings? When the
> > Downloader exports a file into Excel format the date is a text string and
> > can't be changed with date formatting. You need to use datevalue function
> to
> > convert the text string to a number and then you can format the number to
> > whateevr date format you want:
> >
> > =datevalue(03/06/2006) gets 38782; the 38782 can be turned into any date
> > format you want.
> >
> >
> > >From: <ckng001@xxxxxxxxxxxx>
> > >Reply-To: equismetastock@xxxxxxxxxxxxxxx
> > >To: <equismetastock@xxxxxxxxxxxxxxx>
> > >Subject: [EquisMetaStock Group] Re: Changing existing date format (Excel
> > >files)
> > >Date: Thu, 16 Mar 2006 12:45:41 +0800
> > >
> > >Dear all,
> > >
> > >In my Excel files, my expiry date, date of change, date of listing are
> > >found to be in the default US format ie. m/d/yyyy. The dates key-in are
> > >supposed to be in d/m/yyyy (UK format). For eg, 5/1/2006
> (5-January-2006)
> > >previously key-in, is found to be 1-May-2006. Re-formatting the cells to
> > >UK date format did not help. Neither do changing the regional setting in
> > >control panel. Manually inter-changing the day and month is mad. Maybe
> > >there is some way to automate the inter-changing process for these
> previous
> > >dates.
> > >
> > >Thank you.
> > >
> > >Best regards,
> > >ckng
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~-->
Try Online Currency Trading with GFT. Free 50K Demo. Trade
24 Hours. Commission-Free.
http://us.click.yahoo.com/RvFikB/9M2KAA/U1CZAA/BefplB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/equismetastock/
<*> To unsubscribe from this group, send an email to:
equismetastock-unsubscribe@xxxxxxxxxxxxxxx
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
|