3

I have a bunch of stock data in csv's that I am backtesting trading strategies on. The problem is that my strategy buys the open market price if a signal was found yesterday, unfortunately my data is released only at the end of the day meaning I wouldn't know if I was supposed to enter a trade until after market close when the data is released. But because my strategy is trading solely on yesterdays data I think a workaround is to simply append a record to the end of my data representing the next trading day and just show the days price as yesterdays close throughout as to not mess with the profit loss. So for instance say one of my csv's looks like this (albeit not in this format, the actual files have no headers and are comma delimited)

Date     |   Open  |   High  |   Low   |   Close  |  Volume  |
20121228 |  12.23  |  12.80  |  12.23  |   12.60  |  129690  |
20121231 |  13.16  |  13.20  |  12.83  |   13.10  |  141290  |
20130102 |  13.03  |  13.42  |  12.97  |   13.23  |  112390  |
20130103 |  13.23  |  13.80  |  12.23  |   12.60  |  100990  |
20130104 |  12.83  |  12.84  |  12.23  |   12.40  |   89690  |

I would like to append the following record:

20130105 |  12.40  |  12.40  |  12.40  |   12.40  |   89690  |

So I need to increment the date 1, then copy the prior close to the other pricing fields and I think it would be best to just keep the volume the same. This would loop through the folder daily as to add a dummy field to all the files so I can get signals in a more timely manner. And then at the end of each day I have another batch file I already got working to clear out my data folder and overwrite with the true pricing data.

4 Answers 4

1

This might be easier than you thought or I may have oversimplified your need, but I read your question as you'd like to append a CSV file to an existing file and on Windows and most command shell systems a command would read like:

type cat on UNIX, LINUX and OSX NEWCSVFILENAME >> EXISTINGCSVFILE

That is the command, but there is an amount of methodology required since you're also paying for this data it has to be respected. As a trader, you already have some method of backing up your PC data but I encourage you preserve originals, so in the form of a batch file on most windows PC's try this series of commands saved as a batch file:

@echo off

title=Pricing Data Appender executed on %date% at %time% by %username%

copy **EXISTINGCSVFILE** **EXISTINGCSVFILE.%date%_bak** && attrib +r **EXISTINGCSVFILE.%date%_bak**

type *cat on UNIX, LINUX and OSX* **NEWCSVFILENAME** >> **EXISTINGCSVFILE*

title=Pricing Data Appender COMPLETED on %date% at %time% by %username%

And that would make a backup copy of your existing pricing database before edititng it and as a minor safeguard, sets it for ReadOnly attributes to at least warn in the worst circumstance the file shouldn't be deleted and from your shell and various utiltities, protect it from deletion.

If you are being subjected to SarBox or any other regulation that even afflicts small boutique traders, there are a few other commands which will record an independent entry in your systems event log of the process as required by SarbaneOxley Section 404 and the Dodd-Frank act separation of of duty clauses.

I'm interested in helping more, so if you need to elaborate or want to test this and confirm / debug, today is a good day to let me know.

4
  • just reread, Brandon are you also saying the CSV data DOES or DIDN'T include the European style date, so is "20130105" added by you or embedded with the pricing data ?
    – user189950
    Jan 16, 2013 at 17:17
  • This is purely in research stage, and not subject to regulatory scrutiny. No clients will ever see the results derived from this project and should this system ever go live, results will be void of the dummy data and all regulatory measures will be strictly enforced. All original data is currently perserved as well. Jan 16, 2013 at 17:58
  • unfortunately, I dont just need to join two csv's, I am hoping to generate the appended fields within the batch itself. The dates are in the current data, however the batch would be required to calculate the 20130105 field. So from the existing fields the batch needs to add a record in which it increments the date by 1 (yet be smart enough to rollover in to the next month) then copy the last close into the new pricing fields and then just retain the volume. Jan 16, 2013 at 18:03
  • The appended date is always going to be the current date, so perhaps the best method for doing this is simply calling the cmd line for the date and appending it in the european format as opposed to incrementing the prior date. Jan 16, 2013 at 18:07
0

If Data.csv contains:

20121228,12.23,12.80,12.23,12.60,129690
20121231,13.16,13.20,12.83,13.10,141290
20130102,13.03,13.42,12.97,13.23,112390
20130103,13.23,13.80,12.23,12.60,100990
20130104,12.83,12.84,12.23,12.40,89690

and you want to append the following line to it every time the script is run:

20130117,12.40,12.40,12.40,12.40,89690

(i.e. "Current Date (YYYYMMDD),Prev Close,Prev Close,Prev Close,Prev Close,Prev Volume")

Try something like this saved as Update.bat:

@echo off
for /f "usebackq tokens=1,5,6 delims=," %%i in (Data.csv) do (
    REM set /a date=%%i+1 // Wrong way to increment a date!
    set close=%%j
    set volume=%%k
)
echo %date:~6%%date:~3,2%%date:~0,2%,%close%,%close%,%close%,%close%,%volume%>>Data.csv

If you want to run this on multiple CSVs in a directory, replace both instances of Data.csv above with %1. Now to update a single file i.e. Data.csv, you'd use the command:

update Data.csv

To update multiple CSVs, you'd use the command:

for %f in (*.csv) do @update "%f"

Note #1: The exact Date format depends on what you've specified in the Region and Language Control Panel applet. See here for details.

Note #2: I suppose date incrementing and validation logic (including leap year calculation etc.) could be implemented in batch as well (yeah, perhaps if a gun were pointed at my head!), but at that point I would start to question the asker's sanity (besides doubting my own) and seriously suggest that a better (read actual) programming language be used to accomplish this.

5
  • I am open to using another programming language, although I am still unsure of how to implement. I am most comfortable with C++ but I have never had to handle something like this Jan 18, 2013 at 3:20
  • So did you try the batch file? Is it not sufficient?
    – Karan
    Jan 18, 2013 at 6:24
  • Sorry I have been away from the machine I will check it out sometime today. Jan 18, 2013 at 15:35
  • I came across a way to do this using BASH, so I downloaded cygwin on my pc and have been trying to execute from a bat all day and cant figure it out. I either cant get into the correct directory or it finds a syntax error in the 'done' line. Might you have any advice on whats wrong with my code @ echo off start c:\cygwin\bin\bash.exe -1 #!/bin/bash cd "C:/Users/research1/Desktop/Test" ls | while read csvfile do IFS=, read olddate open high low close volume < <(tac $csvfile) read newdate < <(date -d "$olddate + 1 day" +%Y%m%d) echo $newdate,$close,$close,$close,$close,$volume >> $csvfile done Jan 18, 2013 at 22:44
  • Not sure about the Bash code, will have to check. You still haven't said whether the batch file works for you or not.
    – Karan
    Jan 19, 2013 at 15:48
0

If you are familiar with C++, then you can probably handle some C#.

There is no error checking, it changes your csv file without taking a backup and if the csv file is large then it isn't particularly efficient (as it reads the whole file into memory), but should give you the general idea.

If you have Windows 7, then you should have the dotnet framework already, so you can copy this into a file and compile it from the command line using csc filename.cs and it should produce filename.exe.

using System.Collections.Generic;
using System;
using System.Linq;

public class Sample
{

    public static void Main()
    {
        string path = @"c:\temp\temp.csv";

        var lines     = System.IO.File.ReadAllLines(path).ToList();
        var lastLine = lines[lines.Count-1];
        var sections = lastLine.Split(new char[] { ','});

        DateTime lastDate = DateTime.ParseExact(sections[0], "yyyyMMdd" , null );
        DateTime newDate  = lastDate.AddDays(1);
        string newLine    = String.Format("{0},{1},{1},{1},{1},{2}", 
                    newDate.ToString("yyyyMMdd"), sections[4] , sections[5]);
        lines.Add(newLine);

        System.IO.File.WriteAllLines(path, lines.ToArray());

    }

}

However, since you are going to need to do a bit of editing I would recommend downloading LinqPad and just copy the main method into it (and select C# Program) and hit run which should compile it and run it.

0

If you are running Windows 7 (the question tag suggests that), you could achieve that much easier with powershell. Try looking at Import-Csv and Export-Csv commands (the later has a parameter -Append which is much helpful.

If you like, look at the commands documentation:

Import-Csv: https://technet.microsoft.com/en-us/library/ee176874.aspx

Export-Csv: https://technet.microsoft.com/pt-br/library/ee176825.aspx

It would be something like this:

$Path = "C:\PathToFile"
$Objects = Import-Csv $Path
# Here $Objects will be an array of objects, which you can access its 
# properties by the same name in the header of your csv
# you can then do whatever you want in the list:
foreach ($Object in $Objects){
    $Object.Date = $SomeDate
}
# or you can add a new object to the list
$NewObject = [pscustomobject]@{
     Date = Get-Date;
     Open = "12.58";
}
$Objects += $NewObject
# and then finally save it with:
$Objects | Export-csv $Path -Force -NoTypeInformation
# Alternatively, you can add just the object at the end with append, like this
$NewObject | Export-Csv $Path -Append

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .