in Search

Converting .CSV format [RESOLVED]

Last post 11-28-2005, 10:23 by pirx. 7 replies.
Sort Posts: Previous Next
  •  10-28-2005, 5:10 10686

    Converting .CSV format [RESOLVED]

    Hi everyone,

    my data that I get is in .CSV format (ie the file extension is .csv) but there is no option to convert a .CSV in the convert section on the downloader, can anybody help?

    I can only find .xls and .txt....

    From Andrew
  •  10-28-2005, 17:07 10691 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

    Hey Andrew-

    Welcome to the forum!

    Don't worry, data conversion confuses a lot of us. There are some other threads available via the forum search feature that you may want to review too.

    CSV is a formatted text file. As such, you can use the Downloader to convert the data. From the Downloader main menu, select TOOLS | CONVERT. In the Convert Securities dialog box that pops open, change Source file type to ASCII TEXT and change the Destination file type to METASTOCK.

    It does not matter what the file extension is... either *.txt or *.csv is OK. HOWEVER, the csv data in the file must conform to the MetaStock format or your file will not convert. Basically, that means you are limited to 10 columns of data and the date syntax must be exact. This is the exact format:

    Code:
    <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>
    TEST,D,20050601,000000,60.0000,61.8000,59.6600,60.7700,34442,0
    TEST,D,20050602,000000,60.9200,62.2800,60.7700,62.2100,15237,0
    TEST,D,20050603,000000,62.5200,62.5200,60.6000,60.6000,16245,0
    TEST,D,20050606,000000,60.8500,61.6800,59.8900,61.4600,13505,0
    TEST,D,20050607,000000,61.7000,63.0300,61.7000,61.9200,10478,0


    If your data provider does not supply the data in this format you will need to reformat whatever you are receiving. For example, a lot of folks have mentioned difficulties with the date format. I think the simplest way to change the format in bulk is probably to use Excel or a similar program. Excel will open CSV files and from there cell formatting is a breeze. Just make your changes, save your work and then go through the procedure above.
    Traders' Consortium
  •  11-26-2005, 14:07 11904 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

    Hello!

    I´m also stucked with this problem and would apprechiate any help =D>

    The EOD data provider, that I would like to use (besides EODdata), is Dukascopy (http://www.dukascopy.net/english/cquotes/CSV/), because you can download indicies and individual stock symbols with very long historical data.

    Their data formating looks like this:

    Date,Open,High,Low,Close,Volume,Adj. Close*
    22-Nov-05,3.66,3.72,3.59,3.72,206200,3.72
    21-Nov-05,3.55,3.75,3.46,3.65,185200,3.65
    18-Nov-05,3.46,3.68,3.40,3.60,169000,3.60

    g_stockman, you explained it nicely, that in order to import the data into Metastock, it should be formatted exactly like this:

    <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>
    TEST,D,20050601,000000,60.0000,61.8000,59.6600,60.7700,34442,0
    TEST,D,20050602,000000,60.9200,62.2800,60.7700,62.2100,15237,0
    TEST,D,20050603,000000,62.5200,62.5200,60.6000,60.6000,16245,0

    Dukascopy´s data is probably unusable as it is, so that reformatting would be necessary. But one another problem is that all of the data is written in only one column (column A in Excel). I really don´t have an idea, how could I, for example, highlight only dates, copy them into new column (column B) and reformat it accordingly.

    Does anyone know, if there is a way, how to do this? Thanks!
    Active Investor's Blog
  •  11-27-2005, 2:56 11918 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

    Hey Pirx-

    You can convert this format too... I could only figure out how to d/l one symbol at a time and unfortunately, the symbol is not included in the native format. That means that you will have to add the symbol manually before saving the new csv file.

    Select you file to download. Choose Save and d/l it to your disk. Rename the file Raw.csv and open it into Excel. Excel will recoginize the format. If you have any troubles, choose FILE | OPEN | Type="Text Files" | OPEN | Delimited | NEXT | Delimiters="comma" | NEXT | FINISH.

    Note: The worksheet name must be Raw. if you change it, you will have to modify the code.

    Once you have file loaded into Excel, select Alt-F11 and right-click on VBAProject (Raw.csv). Choose INSERT | MODULE and copy the following code into the module. Once completed, close the VBA screen with Alt-Q. Now you are back to your original file. Select Alt-F8 from Excel and this will bring up the macro menu. You should see a macro named convert. Highlight it and press Run. This will convert your raw data into a MS compatible format on a new worksheet named Convert. You will have to add the symbols prior to saving (that sucks) and delete the header row. I included it just so you'd have a reference for comparison.

    Note: Many error checks are not included, so the code will only work for the file format that you supplied to me. It won't even work with other links off the same page that you referenced. Just this one particular format. As always, YOU ARE RESPONSIBLE to ensure that the results are what you desire.

    When you are ready to save, select FILE | SAVEAS | Type="CSV (MS-DOS)" | SAVE. You will get a warning about compatibility, choose Yes to proceed. You are now finished. Just rename your new csv file with a .sym extension.
    Traders' Consortium
  •  11-27-2005, 2:59 11919 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

  •  11-27-2005, 11:49 11933 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

    Hey g_stockman :)

    Thank you so much for this detailed explanation. I apprechiate it and I believe, that your response will be very helpful for many more people.

    This described approach, combined with Dukascopy.net is probably one of the best ways to get FREE historical data for many stock exchanges and stock symbols, with practically unlimited time frame!

    I also found this interesting piece of software, which I currently run on Trial period:
    http://www.ashkon.com/downloader.html

    I think, that combing both, will be more than enough to analyze my stock screen results, which I get using CANSLIM strategy.

    Thanks again!!
    Active Investor's Blog
  •  11-27-2005, 21:27 11938 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

    I also found this interesting piece of software

    Wow, registered version price: USD $49.95. That seems like a lot. If you want to use their data (which they advertise as free), then give me a copy of the raw data format and I will write you an Excel macro to convert that to MS for free. I don't have a lot of spare time, so I'm only offering if you're actually going to use the data for your trading. If you're just curious, then the trial version of the converter sounds like a neat way to check it out.
    Traders' Consortium
  •  11-28-2005, 10:23 11950 in reply to 10686

    Re: Converting .CSV format [RESOLVED]

    Thanks for your kind offer, but you already helped more than enough :)

    They are selling the software for $49.95, which downloads data from various free internet sources, with about 2,5 years of histrical data for daily data - this should be enough for me, I guess. It´s fully automated too.

    Yeah, I´m going to use this for actual trading, but I´m still learning and exploring CANSLIM strategy, which suits me well.

    Have a great day :)
    Active Investor's Blog
View as RSS news feed in XML