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

Notification

Icon
Error

Options
Go to last post Go to first unread
kruupy  
#1 Posted : Friday, October 28, 2005 5:10:56 AM(UTC)
kruupy

Rank: Newbie

Groups: Registered, Registered Users
Joined: 10/27/2005(UTC)
Posts: 6

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
StorkBite  
#2 Posted : Friday, October 28, 2005 5:07:14 PM(UTC)
StorkBite

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 3/19/2005(UTC)
Posts: 2,995

Was thanked: 14 time(s) in 10 post(s)
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:1:4710b90885]<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[/code:1:4710b90885] 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.
pirx  
#3 Posted : Saturday, November 26, 2005 2:07:52 PM(UTC)
pirx

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 10/18/2005(UTC)
Posts: 43
Location: Slovenia, EU

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!
StorkBite  
#4 Posted : Sunday, November 27, 2005 2:56:52 AM(UTC)
StorkBite

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 3/19/2005(UTC)
Posts: 2,995

Was thanked: 14 time(s) in 10 post(s)
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.
StorkBite  
#5 Posted : Sunday, November 27, 2005 2:59:04 AM(UTC)
StorkBite

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 3/19/2005(UTC)
Posts: 2,995

Was thanked: 14 time(s) in 10 post(s)
[code:1:54cbcc9f7f]Sub Convert() 'Create new worksheet If NewSheet("Convert") = True Then MsgBox "Sheet already exists!" Exit Sub End If Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "Convert" 'Delete extraneous data Columns("G:G").Delete Shift:=xlToLeft 'Adjust column width for data input Columns("A:J").ColumnWidth = 12 'Create workspace for column headers Rows("1:1").Insert Shift:=xlDown 'Set column headers and formatting With Range("a1") .FormulaR1C1 = "TICKER" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("b1") .FormulaR1C1 = "PERIOD" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("c1") .FormulaR1C1 = "DATE" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("d1") .FormulaR1C1 = "TIME" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("e1") .FormulaR1C1 = "OPEN" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("f1") .FormulaR1C1 = "HIGH" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("g1") .FormulaR1C1 = "LOW" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("h1") .FormulaR1C1 = "CLOSE" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("i1") .FormulaR1C1 = "VOL" .Font.Bold = True .HorizontalAlignment = xlLeft End With With Range("j1") .FormulaR1C1 = "OI" .Font.Bold = True .HorizontalAlignment = xlLeft End With 'Copy raw date Sheets("Raw").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Convert").Select Range("C2").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .NumberFormat = "mm/dd/yyyy" End With 'Calculate number of records for further formatting Dim intRecords As Integer intRecords = Selection.Count Dim intCounter As Integer intCounter = 0 'Validate and convert raw date Do While intCounter < intRecords With Range("C2").Offset(intCounter, 0) Select Case ValidateDate(.Value) Case True .Value = Val(Format(.Text, "yyyymmdd")) .NumberFormat = "0" Case False MsgBox "Invalid date format" 'Further management would need to go here, if desired End Select End With intCounter = intCounter + 1 Loop intCounter = 0 'Copy raw open Sheets("Raw").Select Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Convert").Select Range("E2").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .NumberFormat = "0.00" End With 'Copy raw high Sheets("Raw").Select Range("C2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Convert").Select Range("F2").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .NumberFormat = "0.00" End With 'Copy raw low Sheets("Raw").Select Range("D2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Convert").Select Range("G2").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .NumberFormat = "0.00" End With 'Copy raw close Sheets("Raw").Select Range("E2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Convert").Select Range("H2").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .NumberFormat = "0.00" End With 'Copy raw volume Sheets("Raw").Select Range("F2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Convert").Select Range("I2").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .NumberFormat = "0" End With 'Ticker needs to be implemented or manually entered Do While intCounter < intRecords With Range("A2").Offset(intCounter, 0) .Value = "Unk" .HorizontalAlignment = xlLeft End With intCounter = intCounter + 1 Loop intCounter = 0 'Create period Do While intCounter < intRecords With Range("B2").Offset(intCounter, 0) .Value = "D" .HorizontalAlignment = xlLeft End With intCounter = intCounter + 1 Loop intCounter = 0 'Create time Do While intCounter < intRecords With Range("D2").Offset(intCounter, 0) .Value = "0" .NumberFormat = "000000" .HorizontalAlignment = xlLeft End With intCounter = intCounter + 1 Loop intCounter = 0 'Create open interest Do While intCounter < intRecords With Range("j2").Offset(intCounter, 0) .Value = "0" .NumberFormat = "0" .HorizontalAlignment = xlLeft End With intCounter = intCounter + 1 Loop intCounter = 0 'Postition cursor for new input Range("A2").Select End Sub Function ValidateDate(ByVal DateEntry) As Boolean Dim bTest As Boolean bTest = IsDate(DateEntry) If Not bTest Then ValidateDate = False Exit Function End If ValidateDate = True End Function Function NewSheet(ByVal SheetName) As Boolean Dim ws As Worksheet For Each ws In Sheets If ws.Name = SheetName Then NewSheet = True Exit Function End If NewSheet = False Next ws End Function [/code:1:54cbcc9f7f]
pirx  
#6 Posted : Sunday, November 27, 2005 11:49:35 AM(UTC)
pirx

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 10/18/2005(UTC)
Posts: 43
Location: Slovenia, EU

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
StorkBite  
#7 Posted : Sunday, November 27, 2005 9:27:15 PM(UTC)
StorkBite

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 3/19/2005(UTC)
Posts: 2,995

Was thanked: 14 time(s) in 10 post(s)
Quote:
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.
pirx  
#8 Posted : Monday, November 28, 2005 10:23:06 AM(UTC)
pirx

Rank: Advanced Member

Groups: Registered, Registered Users
Joined: 10/18/2005(UTC)
Posts: 43
Location: Slovenia, EU

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 :)
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.