I have received a large number of questions and suggestions about new data formatting options in YGQD. For a while I tried to keep up by adding new formatting settings, but it’s obvious that this is not a good long-term solution, as it clogs up the UI and makes the product more difficult to configure and use. So I decided to address this somewhat differently (and I hope more elegantly) in the next release (current 2.8.0.2, next 2.8.1.0 most likely).
In the meantime, you can use the DOS script below (or a modified version of it) to generate virtually any output data format.
rem 1. sed exe file:
set sed=”C:\Program Files\GnuWin32\bin\sed.exe”
rem 2. ygqd data path:
set quotesdir=”C:\data\”
rem 3. sed transformation rule
set script=s/\(.*\),.*,.*,.*,\(.*\),.*/\1,\2/g
@echo on
rem 4. the command performing the actual transformation.
for /f “usebackq delims=$” %%i ^
in (`dir /A:-D /B /S %quotesdir%*.csv` ) ^
do %sed% %script% “%%~i” > “%%~dpni.csv.transf”
This script is based on an actual request from a user who needed the output to contain only the date and the closing price.
This DOS script will take all the csv files generated by YGQD in a download session and generate files containing only the date and the closing price. The resulting files have the extension .transf. The assumption is that the YGQD data files have the following format: date,open,high,low,close,volume.
The only pre-requisite for this script to work is to have Sed installed on your computer. Very briefly, Sed is a free, powerful open source “stream editor” – it will take input text, apply certain transformation rules and output the result. In fact many users have taken Sed far beyond this initial purpose and developed quite amazing apps based on it – check out the available resources on Sed in general or on the Windows port for more info.
Here’s how the script works (the points below will follow the comment numbers 1-4 in the script)
- set the value of a variable named sed to the full path of the Sed executable.
- set the value of a variable named quotesdir to the path containing the output files generated by YGQD. The files can be flat or in a nested structure
- set the value of a variable called script to the string defining the transformation rule. Sed will read the input file and apply the rule on a line by line basis. This rule has the following components separated by backslashes
- the sed substitute command: s (substitute)
- the matching pattern (regular expression): \(.*\),.*,.*,.*,\(.*\),.* . Regular expression matching is a subject in its own right, so please consult the Sed documentation or the many other available resources for details. Basically, this expression matches the output data format (date, open etc) and defines two tagged subexpressions (the ones between parantheses) corresponding only to the date and closing price, which will be used in the replacement pattern.
- the replacement pattern: \1,\2 – this indicates that the output will contain the first match (date) followed by “,”, followed by the second match (closing price)
- flags: g (apply the replacement to all matches, not just the first)
- the actual command: for each file in the specified directory and subdirectories call sed with the file name, the script (transformation rules) and send the output to a file with matching name but with extension .transf.
It is possible to modify the script to replace the original files instead of generating new files, but that would effectively disable the YGQD Update setting. However, if that is not a concern (if for example you are downloading only a limited number of files, or only for a limited time range), it is relatively easy to change the script to generate the transformed content directly into the original files.
Please post your own transformation rules or variations to this script that you think may interest other users.