logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error
Constant error in mass converting csv data to metastock format - Constant error in mass converting csv data to metastock format
Options
Go to last post Go to first unread
Previous Topic Next Topic
jonnyyyl  
#1 Posted : Friday, May 30, 2014 1:09:03 PM(UTC)
jonnyyyl

Rank: Newbie

Groups: Registered, Registered Users, Unverified Users
Joined: 7/6/2013(UTC)
Posts: 2
So I have CSV files that looks like this:

UserPostedImage


This format is applied universally to all share price data of over a few companies. However, upon mass conversion, I persistantly receive and error #864:Missing/Invalid date.

In txt, they look like this:

Date,Open,High,Low,Close,Volume,Open Interest
06/16/04,0.875,0.925,0.815,0.83,2198875040,0
06/17/04,0.83,0.875,0.825,0.845,419007520,0
06/18/04,0.84,0.85,0.79,0.805,182990000,0
06/21/04,0.825,0.825,0.79,0.8,114085000,0
06/23/04,0.81,0.89,0.805,0.885,275080000,0
06/24/04,0.895,0.905,0.865,0.895,173615000,0
06/25/04,0.895,0.91,0.87,0.88,106062500,0
06/28/04,0.875,0.88,0.85,0.86,73730000,0


Upon some research, apparently I missed something about a headerline...where do I find that information? what should the csv/excel file look like before conversion? Is there a sample I can refer to?

Most grateful.
Back to top

Wanna join the discussion?! Login to your Discussions forum accountor Register a new forum account.
Back to top  
retireewill  
#2 Posted : Friday, May 30, 2014 1:44:45 PM(UTC)
retireewill

Rank: Member

Groups: Registered, Registered Users, Unverified Users
Joined: 5/1/2012(UTC)
Posts: 25
Location: Sherman, Texas
DSLV,D,05/30/2014,51.7700,53.7500,51.8650,53.7110,121,121
DUST,D,05/30/2014,29.7700,30.4500,29.1700,30.2400,9707,9707
CURE,D,05/30/2014,83.8700,84.0000,83.2009,84.0000,106,106
TZA,D,05/30/2014,16.2600,16.4400,16.2200,16.3000,63838,63838
SOXL,D,05/30/2014,94.4500,95.0000,94.0401,94.8700,118,118
-------
Back to top
MS Support  
#3 Posted : Friday, May 30, 2014 6:49:45 PM(UTC)
MS Support

Rank: Advanced Member

Groups: Moderators, Registered, Registered Users, Subscribers
Joined: 10/8/2010(UTC)
Posts: 1,960

Thanks: 92 times
Was thanked: 155 time(s) in 150 post(s)
I believe the Open Interest header is supposed to be <o/i> per the ASCII File Layout topic below:

In order for an ASCII source file to convert properly, the file must be arranged correctly. This section explains the proper alignment.

The data in the ASCII file should be arranged as shown below.

<ticker>, <per>, <date>, <time>, <open>, <high>, <low>, <close>, <vol>, <o/i>

An ASCII source file can also contain a <name> field. This is helpful if your destination file is in the MetaStock format and you want the name of MetaStock file to be different than the ticker symbol . If you choose to include a <name> field in your ASCII file, you must define a header line, otherwise the conversion will fail. The following header line is an example.

<ticker>, <name>, <per>, <date>, <close>, <vol>

<ticker> The ticker symbol of the security. The ticker symbol is used to determine which MetaStock data file to post the data to. The ticker symbol (if you choose to specify it in the ASCII file) must be included on every line of data.

If the ticker symbol in the ASCII file does not match a ticker symbol in the MetaStock data files, you will be asked if you want to create a new MetaStock data file.

If the ticker symbol in the ASCII file includes a space, a comma-delimited header line must be created at the top of the ASCII file (see ASCII Source File Layout on how to create a header line). The header line allows the file to be processed using only commas as delimiter characters.

The ticker field can be omitted, but only if the <per> field is also omitted. If the ticker symbol is omitted, you are prompted to supply the ticker symbol (see Ticker Symbol Dialog).

<per> The periodicity of the data (e.g., Intraday, Daily, Weekly, Monthly, or Yearly).

If this field is omitted, the periodicity selected in the Conversion Options dialog is used (see Conversion Options Dialog).

<date> The date of the data contained on the line of the ASCII file. Each line of the ASCII file must have a date. The date can be in either the yyyymmdd or mm/dd/yyyy formats (e.g., 19891222 or 12/22/1989), and can use either 2-digit or 4-digit years.

When using 2-digit years in Windows 95 or Windows NT 4.0, The DownLoader will assume any number preceding "30" should begin with "20", years "30" and higher will begin with "19." When using 2-digit years in Windows 98 or Windows 2000, The DownLoader will use the Regional Settings found in your Windows Control Panel.

The dates must be in ascending order (i.e., most recent date is listed on the last line).

If the dates in the ASCII file precede the dates in the existing MetaStock file (i.e., the file you are appending to), the data will be ignored.

<time>. The time of the data contained on the line of the ASCII file. This field should only be used if the ASCII file contains intraday price data (i.e., the periodicity is intraday). The time should be entered using a 24 hour clock (e.g., 3:30 PM = 15:30).

<open> The opening price of the security. The open field can be omitted if the open interest (<o/i>) field is also omitted.

<high> The high price for this date.

<low> The low price for this date.

<close> The closing (last) price for this date.

<vol> The volume for this date.

<o/i> The open interest for this date. The open interest field can be omitted if the open (<open>) field is also omitted.

EXAMPLE INPUT:

IBM,D,7/29/86,121,122.375,120.500,121.75,624589,0 IBM,D,7/30/86,123.375,123.125,122.5,122.75,431234,0

MOB,D,7/29/86,28.25,29.5,27.125,28,12627,0

MOB,D,7/30/86,30,30.625,29.875,30.375,7588,0

Because the <open> and <o/i> fields are optional, this data also could be arranged as shown below.

IBM,D,7/29/86,122.375,120.500,121.75,624589

IBM,D,7/30/86,123.125,122.5,122.75,431234

MOB,D,7/29/86,29.5,27.125,28,12627

MOB,D,7/30/86,30.625,29.875,30.375,7588

If all the data in the ASCII file is for one security only, then the <ticker> and <per> fields can be omitted. The data can be arranged as shown below. You will be prompted for the ticker symbol.

7/27/96,41.375,40.5,40.75,524589

7/38/96,42.5,40.5,41.75,345679

7/29/96,42.375,41.125,41.75,678967

7/30/96,43.625,42.500,43.125,564479

If all the data in the ASCII file is for one security only, then the <ticker> ,<per>,<high>,<low>, and <vol> fields can be omitted. The data can be arranged as shown below. You will be prompted for the ticker symbol.

7/27/96,40.75

7/38/96,41.75

7/29/96,41.75

7/30/96,43.125

In the resulting MetaStock files, the other price fields will be automatically filled with the close values. The volume fields will be filled with zeros.

A header line can be placed at the top of the ASCII file to specify the arrangement of the data. This allows you to arrange the data in any order you wish. The line below shows volume appearing before the price fields.

<date>,<vol>,<high>,<low>,<close>

7/29/86,624589,122.375,120.500,121.75

7/30/86,431234,123.125,122.5,122.75

7/29/86,12627,29.5,27.125,28

7/30/86,7588,30.625,29.875,30.375
Back to top
jonnyyyl  
#4 Posted : Friday, May 30, 2014 10:33:33 PM(UTC)
jonnyyyl

Rank: Newbie

Groups: Registered, Registered Users, Unverified Users
Joined: 7/6/2013(UTC)
Posts: 2
Many thanks for the reply.

I am still slightly confused.
1. How do I know precisely under which circumstances to include different headers?

2. For <per> and <time> headers, is there a list of all annotations (ie "d" denotes Days, "M" stands for months, ticks, intraday etc.)? As for <time> are there some examples I can be refered to? The examples above didn't include any.

3. So which headers must be included then, if i have say 100 csv files, each file contains price data of only 1 security each?

Based on the instructions, here's what I got so far, the raw csv file:

UserPostedImage

will this suffice?
thanks.
Back to top
Laisze  
#5 Posted : Saturday, May 31, 2014 7:54:02 AM(UTC)
Laisze

Rank: Advanced Member

Groups: Registered, Registered Users, Subscribers
Joined: 11/18/2007(UTC)
Posts: 96
Location: HK
MS Support wrote:
When using 2-digit years in Windows 95 or Windows NT 4.0, The DownLoader will assume any number preceding "30" should begin with "20", years "30" and higher will begin with "19." When using 2-digit years in Windows 98 or Windows 2000, The DownLoader will use the Regional Settings found in your Windows Control Panel.


Bbbut... what about when using DOS, or CP/M? [:'(]
Back to top
MS Support  
#6 Posted : Monday, June 2, 2014 3:34:43 PM(UTC)
MS Support

Rank: Advanced Member

Groups: Moderators, Registered, Registered Users, Subscribers
Joined: 10/8/2010(UTC)
Posts: 1,960

Thanks: 92 times
Was thanked: 155 time(s) in 150 post(s)
jonnyyyl wrote:
Many thanks for the reply.

I am still slightly confused.
1. How do I know precisely under which circumstances to include different headers?

2. For <per> and <time> headers, is there a list of all annotations (ie "d" denotes Days, "M" stands for months, ticks, intraday etc.)? As for <time> are there some examples I can be refered to? The examples above didn't include any.

3. So which headers must be included then, if i have say 100 csv files, each file contains price data of only 1 security each?

Based on the instructions, here's what I got so far, the raw csv file:

UserPostedImage

will this suffice?
thanks.




Hi, sorry for the information overload, but not all fields are required. Periodicity is optional and can be safely omitted. Ticker is also optional but the conversion will prompt you for a symbol for the security file if the ASCII/CSV does not contain one. Time is really only for Intraday conversions so for Daily data you can ignore this field as well.

You may also need to change <Volume> to <Vol> but otherwise looks good.
Back to top
Users browsing this topic
Guest (Hidden)
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF.NET | YAF.NET © 2003-2024, Yet Another Forum.NET