Batch script to append record to csv files

Question

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.

Solution

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.

Comments

[Brandon Ogle]

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.

[Brandon Ogle]

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.

[Brandon Ogle]

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

[Karan]

So did you try the batch file? Is it not sufficient?

[Brandon Ogle]

Sorry I have been away from the machine I will check it out sometime today.

[Brandon Ogle]

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

[Karan]

Not sure about the Bash code, will have to check. You still haven't said whether the batch file works for you or not.