|
PureBytes Links
Trading Reference Links
|
norm,
dont ask me why...it works.
1. The excel sheet d/l from yahoo has dates arranged in descending
order (latest at the top). Use th sort command and sort as 'ascending'
based on the DATE colomn.
2. MS uses mm/dd/yyyy. yahoo provides dd/mm/yyyy. except that it doesnt know it ;-). Apply the dd/mm/yyyy format to the sorted data.
3.
open new security from the downloader. be careful about name and code
since this has to match whatever convention your data downloader uses.
else geting automatic updtaes is a problem.
4. copy the excel data base and onto the new security data area. the date format automatically changes to mm/dd/yyyy. (go figure!)
5. save and you are up and running.
its been a while since i did this. in case 4. doesnt work, then after 2.
above apply the mm/dd/yyyy format on the date coloum. if the format is
not readily available, create it on the 'custom' list. every other
precaution listed by dusant and ferriera needs to be observed.
warm regards,
eswar
On 5/3/05, MG Ferreira <quant@xxxxxxxxxxxxxxxx> wrote:
Norm,
Here is another idea. When you import the CSV file from Yahoo!,
ensure you import the date column as TEXT so as to ensure Excel does
not get clever with it. Now, create three new columns, and give them
headings YYYY, MM and DD. Since Yahoo! gives the data in M/D/Y format
I think something like 'left(A1,2)' will give the month, so put this
in the month column (adjust A1 accordingly). Also, use 'right(A1,4)'
to extract the year and 'mid(A1,3,2)' or similar to get the day. I
once had to look up the position of the '/' to get this to work, but
this is a good technique to have available.
Once you have the Y, M and D, you can use e.g. the 'date' function to
make a correct Excel date, or reformat it, say
=Y&'-'&M&'-'&D
where Y, M and D is the relevant cell.
Regards
MG Ferreira
TsaTsa EOD Programmer and trading model builder
http://www.ferra4models.com
http://fun.ferra4models.com
--- In equismetastock@xxxxxxxxxxxxxxx, "normhe2000" <norm@xxxx> wrote:
> Thank you Dusant for your message. I went into the regional date
> settings and changed both the short and long date format to mm/dd/yy
> and gave it a go. Unfortunately, I still got the "invalid date" when
> I tried to save it in Downloader. It's strange but I didn't have
> this problem before - so I must be doing something different - what?
> is the question. I am using Win XP PRo which I have used before.
>
> I have used Netfile to download daily quotes and they go fine (as
> you know, Netfile gets the data from Yahoo). It seems the historical
> data from Yahoo to Excel as a .csv file causes the problem with the
> date. I have played with all the different date formats in excel but
> to no avail.
>
> Any other ideas would be greatly appreciated. Your time and thoughts
> are most appreciated.
>
> Thanks. Norm.
>
> --- In equismetastock@xxxxxxxxxxxxxxx, Dusant <cooldush@xxxx> wrote:
> > This has something to do with your regional setting in the control
> panel.
> >
> > Change your basic date format to mm/dd/yy, and the problem shud be
> fixed.
> >
> > Dusant
> > ----- Original Message -----
> > From: normhe2000
> > To: equismetastock@xxxx
> > Sent: Sunday, May 01, 2005 9:18 AM
> > Subject: [EquisMetaStock Group] Date Entry copied from Excel
> >
> >
> > I am a real newby trying Metastock. I have taken historical stock
> > data from Yahoo and downloaded to excel. I am trying to copy it
> to the
> > particular stock in downloader. I keep getting an "invalid date"
> so
> > hence cannot save it. The booklet says to use mm/dd/yy but the
> date
> > data in downloader seems to be dd/mm/yyyy. I have tried a variety
> of
> > date formats from excel but keep getting "invalid date".
> >
> > Any help would be appreciated with this frustrating and very
> basic
> > issue.
> >
> > Thanks. Norm.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ---------------------------------
> > 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 the Yahoo! Terms of
> Service.
> >
> >
> > Yahoo! India Matrimony: Find your life partneronline.
Yahoo! Groups Links
|