Welcome Guest! To enable all features please try to register or login.
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: 3,003

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

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: 3,003

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: 3,003

[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

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: 3,003

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

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

Notification

Icon
Error