convert file with 15 minute stock market data to hourly and daily

[purpleglow]

Hi!

I have a text file with 15 minute stock market data. I'd like a simple batch file to convert this into both hourly and daily data and write it to a text file.

1. Each line is data for 15 minutes.
2. The data is strings separated by commas.
3. The first string is the date. (for example, the date on the first line is september 1st 2017)
4. The second string is the time (for example, the time on the first line is 9:30 AM)
5. The 3rd is the price of the security at the open of this 15 minute interval.
6. The 4th is the highest price of the interval.
7. The 5th is the lowest price of the interval.
8. The 6th is the closing price of the interval.
9. The 7th and last is the trading volume for the interval.

Here is some sample data:

Code: Select all

2017.09.01,09:30,247.93,248.08,247.85,247.98,375774
2017.09.01,09:45,247.99,248.12,247.69,247.85,248224
2017.09.01,10:00,247.85,247.93,247.68,247.89,357934
2017.09.01,10:15,247.89,247.94,247.79,247.94,197296
2017.09.01,10:30,247.93,248.03,247.85,247.87,142138
2017.09.01,10:45,247.86,248.17,247.86,248.13,186712
2017.09.01,11:00,248.12,248.19,248.06,248.19,221545
2017.09.01,11:15,248.19,248.19,247.91,247.92,225323
2017.09.01,11:30,247.92,247.96,247.85,247.94,172936
2017.09.01,11:45,247.95,248.11,247.93,248.09,93343
2017.09.01,12:00,248.10,248.13,248.05,248.13,112756
2017.09.01,12:15,248.14,248.21,248.12,248.17,93685
2017.09.01,12:30,248.16,248.21,248.13,248.18,60979
2017.09.01,12:45,248.19,248.32,248.19,248.28,98539
2017.09.01,13:00,248.28,248.32,248.22,248.29,44581
2017.09.01,13:15,248.30,248.30,248.17,248.23,89534
2017.09.01,13:30,248.23,248.48,248.21,248.21,102550
2017.09.01,13:45,248.22,248.22,248.13,248.17,75672
2017.09.01,14:00,248.17,248.25,248.13,248.14,57446
2017.09.01,14:15,248.15,248.17,248.10,248.12,62497
2017.09.01,14:30,248.13,248.17,248.09,248.17,68727
2017.09.01,14:45,248.17,248.20,248.14,248.16,106971
2017.09.01,15:00,248.16,248.20,248.11,248.14,73498
2017.09.01,15:15,248.14,248.23,248.14,248.20,112317
2017.09.01,15:30,248.20,248.20,248.07,248.09,171255
2017.09.01,15:45,248.10,248.16,247.87,247.88,953060
2017.09.05,09:30,247.24,247.51,246.94,247.05,376549
2017.09.05,09:45,247.07,247.38,247.00,247.37,271207
2017.09.05,10:00,247.38,247.47,247.17,247.34,311357
2017.09.05,10:15,247.34,247.47,247.07,247.12,211352
2017.09.05,10:30,247.12,247.14,246.69,246.74,385340
2017.09.05,10:45,246.75,246.86,246.61,246.83,252970
2017.09.05,11:00,246.83,246.88,246.64,246.80,222284
2017.09.05,11:15,246.81,246.83,246.47,246.62,193791
2017.09.05,11:30,246.61,246.85,246.58,246.78,185288
2017.09.05,11:45,246.78,246.82,246.64,246.76,88213
2017.09.05,12:00,246.75,246.75,245.75,245.81,601313
2017.09.05,12:15,245.81,246.07,245.71,245.73,416209
2017.09.05,12:30,245.73,245.74,245.29,245.58,434979
2017.09.05,12:45,245.59,245.60,245.30,245.54,327573
2017.09.05,13:00,245.56,245.56,244.95,245.04,545889
2017.09.05,13:15,245.03,245.59,244.99,245.41,375058
2017.09.05,13:30,245.41,245.68,245.18,245.56,357450
2017.09.05,13:45,245.56,245.77,245.47,245.75,140992
2017.09.05,14:00,245.74,245.98,245.69,245.77,254931
2017.09.05,14:15,245.77,246.15,245.61,246.06,258095
2017.09.05,14:30,246.06,246.12,245.86,245.95,357772
2017.09.05,14:45,245.95,246.01,245.71,245.82,269652
2017.09.05,15:00,245.82,246.00,245.58,245.86,255123
2017.09.05,15:15,245.86,246.11,245.75,246.09,290512
2017.09.05,15:30,246.09,246.24,245.80,245.90,352441
2017.09.05,15:45,245.90,246.18,245.81,246.09,840628
2017.09.06,09:30,246.85,246.99,246.62,246.72,346852
2017.09.06,09:45,246.72,246.84,246.60,246.78,347391
2017.09.06,10:00,246.76,247.08,246.75,246.84,326253
2017.09.06,10:15,246.82,246.88,246.53,246.64,308840
2017.09.06,10:30,246.65,246.77,246.51,246.59,288759
2017.09.06,10:45,246.59,246.72,246.24,246.69,488003
2017.09.06,11:00,246.68,246.75,246.23,246.28,214781
2017.09.06,11:15,246.26,246.54,246.26,246.50,126161
2017.09.06,11:30,246.49,246.61,246.39,246.53,126611
2017.09.06,11:45,246.53,246.61,246.42,246.59,90696
2017.09.06,12:00,246.59,246.61,246.43,246.59,97533
2017.09.06,12:15,246.59,246.75,246.53,246.70,163039
2017.09.06,12:30,246.70,246.72,246.57,246.69,77694
2017.09.06,12:45,246.67,246.86,246.66,246.81,166389
2017.09.06,13:00,246.83,247.03,246.81,246.97,197451
2017.09.06,13:15,246.96,247.01,246.85,246.88,127802
2017.09.06,13:30,246.88,246.99,246.88,246.93,133083
2017.09.06,13:45,246.95,246.97,246.83,246.87,44317
2017.09.06,14:00,246.87,247.10,246.86,247.02,89563
2017.09.06,14:15,247.02,247.19,246.99,247.10,119502
2017.09.06,14:30,247.08,247.14,247.02,247.02,112057
2017.09.06,14:45,247.03,247.12,246.96,247.07,186626
2017.09.06,15:00,247.08,247.18,247.00,247.12,125642
2017.09.06,15:15,247.12,247.28,247.11,247.25,196488
2017.09.06,15:30,247.24,247.25,247.03,247.05,338538
2017.09.06,15:45,247.05,247.11,246.80,246.91,807924

for a conversion from 15 minute to daily I had an idea of something like:

Code: Select all

@echo off
currentdate = 0
xhigh = 0
xlow = 0
xclose = 0
xvolume = 0
for /F "delims=," %%G in (SPY15.txt) do (
set date = %%G
:: time is ignored for 15 minute to daily conversion
set time = %%H
set open = %%I
set high = %%J
set low = %%K
set close = %%L
set volume = %%M
if currentdate = 0 then currentdate = date
if currentdate != date then
    :: data for the day has been processed so output 1 line of processed data to the output file
    currentdate = date
else
    :: process data
    :: open never needs to be processed because it is always the first open for the given date
    if high greater than xhigh then xhigh = high
    if low is lower than xlow then xlow = low
    :: close never needs to be processed because it is always the last close for the given date
    :: add up the volumes for the day
end
)

15 minute to hourly is a little more complex because you must break down the time variable. The correct breakdown for NORMAL hourly data is:

1st hour: 9:30, 9:45, 10:00, 10:15
2nd hour: 10:30, 10:45, 11:00, 11:15
3rd hour: 11:30, 11:45, 12:00, 12:15
4th hour: 12:30, 12:45 13:00, 13:15
5th hour: 13:30, 13:45, 14:00, 14:15
6th hour: 14:30, 14:45, 15:00, 15:15
7th hour: 15:30, 15:45

So you have 4 data points for each hour except the last one which has just 2 data points BUT only for normal days.

Once in a while, the market will be open for a half day, so there can be gaps. If the market closes at noon, the last data point will be 11:45 for that day.

Thanks in advance.

[pieh-ejdsch]

Hello purpleglow,

is this an Example?

Code: Select all

@echo off
setlocal enabledelayedExpansion
set prompt=$g$s
for /f "delims==" %%i in ('2^>nul set day[') do set "%%i="
set "day-=" 
for /f "usebackQtokens=1-5*delims=,.:" %%a in ("%userprofile%\desktop\.txt") do (
 set /a "hour =(1%%d%%e -10830) /100"
 for /f "tokens=1-6*delims=," %%f in ("%%a%%b%%c,!hour!,%%f") do (
  if defined day- if !day-! NEQ %%f ( set day[!day-!
   for /f "usebackQdelims==" %%i in (`set day[!day-!`) do set "%%i="
  )
  set day-=%%f
  if NOT defined day[%%f]---open   ( set day[%%f]---open=%%h
   set day[%%f]--low--=%%j
  )
  if NOT defined day[%%f]%%g--open ( set day[%%f]%%g--open=%%h
   set day[%%f]%%g-low--=%%j
  )
  if !day[%%f]--hight! lss %%i      set day[%%f]--hight=%%i
  if !day[%%f]%%g-hight! lss %%i    set day[%%f]%%g-hight=%%i
  if !day[%%f]--low--! gtr %%j      set day[%%f]--low--=%%j
  if !day[%%f]%%g-low--! gtr %%j    set day[%%f]%%g-low--=%%j
  set day[%%f]--close=%%j
  set day[%%f]%%g-close=%%k
  set volume=
  
))
set day[!day-!
for /f "delims==" %%i in ('2^>nul set day[') do set %%i=
pause
exit /b

Phil

[purpleglow]

Well for example, the output for the 15 minute to daily conversion would be:

2017.09.01,09:30,247.93,248.30,247.68,247.88,(sum of all volumes from september 1st)
2017.09.05,09:30,247.24,247.51,244.95,246.09,(sum of all volumes from september 5th)
2017.09.06.09:30,246.85,247.28,246.23,246.91,(sum of all volumes from september 6th)

Each line is:

date,time,first number in column 3, biggest number in column 4, smallest number in column 5, last number in column 6, add up all the volumes found in column 7

[Aacini]

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set "dayDate="
set "hour=0"
(for /F "tokens=1-7 delims=," %%a in (input.txt) do (

   rem Get row elements
   set "theDate=%%a"
   set "theTime=%%b"
   set "open=%%c"
   set "highest=%%d" & set "highest=!highest:.=!"
   set "lowest=%%e"  & set "lowest=!lowest:.=!"
   set "closing=%%f"
   set "volume=%%g"

   rem Output day results when the day change, and reset they
   if "!dayDate!" neq "!theDate!" (
      if defined dayDate echo !dayDate!,!dayTime!,!dayOpen!,!dayHighest:~0,-2!.!dayHighest:~-2!,!dayLowest:~0,-2!.!dayLowest:~-2!,!dayClosing!,!dayVolume!
      set "dayDate=!theDate!"
      set "dayTime=!theTime!"
      set "dayOpen=!open!"
      set "dayHighest=!highest!"
      set "dayLowest=!lowest!"
      set "dayClosing=!closing!"
      set "dayVolume=0"
      if !hour! gtr 0 (
         echo !hourDate!,!hourTime!,!hourOpen!,!hourHighest:~0,-2!.!hourHighest:~-2!,!hourLowest:~0,-2!.!hourLowest:~-2!,!closing!,!hourVolume! >&2
         set "hour=0"
      )
   )

   rem Process data
   if !highest! gtr !dayHighest! set "dayHighest=!highest!"
   if !lowest! lss !daylowest! set "daylowest=!lowest!"
   if !highest! gtr !hourHighest! set "hourHighest=!highest!"
   if !lowest! lss !hourlowest! set "hourlowest=!lowest!"
   set "dayClosing=!closing!"
   set /A dayVolume+=volume, hourVolume+=volume

   rem Output hour results every 4 lines, and reset they
   set /A "hour=(hour+1)%%4"
   if !hour! equ 1 (
      set "hourDate=!theDate!"
      set "hourTime=!theTime!"
      set "hourOpen=!open!"
      set "hourHighest=!highest!"
      set "hourLowest=!lowest!"
      set "hourVolume=!volume!"
   ) else if !hour! equ 0 (
      echo !hourDate!,!hourTime!,!hourOpen!,!hourHighest:~0,-2!.!hourHighest:~-2!,!hourLowest:~0,-2!.!hourLowest:~-2!,!closing!,!hourVolume! >&2
   )
)) > daily.txt  2> hourly.txt

echo !dayDate!,!dayTime!,!dayOpen!,!dayHighest:~0,-2!.!dayHighest:~-2!,!dayLowest:~0,-2!.!dayLowest:~-2!,!dayClosing!,!dayVolume! >> daily.txt
if %hour% gtr 0 echo !hourDate!,!hourTime!,!hourOpen!,!hourHighest:~0,-2!.!hourHighest:~-2!,!hourLowest:~0,-2!.!hourLowest:~-2!,!closing!,!hourVolume! >> hourly.txt

hourly.txt:

Code: Select all

2017.09.01,09:30,247.93,248.12,247.68,247.94,1179228 
2017.09.01,10:30,247.93,248.19,247.85,247.92,775718 
2017.09.01,11:30,247.92,248.21,247.85,248.17,472720 
2017.09.01,12:30,248.16,248.32,248.13,248.23,293633 
2017.09.01,13:30,248.23,248.48,248.10,248.12,298165 
2017.09.01,14:30,248.13,248.23,248.09,248.20,361513 
2017.09.01,15:30,248.20,248.20,247.87,247.05,1124315 
2017.09.05,09:30,247.24,247.51,246.94,247.12,1170465 
2017.09.05,10:30,247.12,247.14,246.47,246.62,1054385 
2017.09.05,11:30,246.61,246.85,245.71,245.73,1291023 
2017.09.05,12:30,245.73,245.74,244.95,245.41,1683499 
2017.09.05,13:30,245.41,246.15,245.18,246.06,1011468 
2017.09.05,14:30,246.06,246.12,245.58,246.09,1173059 
2017.09.05,15:30,246.09,246.24,245.80,246.72,1193069 
2017.09.06,09:30,246.85,247.08,246.53,246.64,1329336 
2017.09.06,10:30,246.65,246.77,246.23,246.50,1117704 
2017.09.06,11:30,246.49,246.75,246.39,246.70,477879 
2017.09.06,12:30,246.70,247.03,246.57,246.88,569336 
2017.09.06,13:30,246.88,247.19,246.83,247.10,386465 
2017.09.06,14:30,247.08,247.28,246.96,247.25,620813 
2017.09.06,15:30,247.24,247.25,246.80,246.91,1146462 

daily.txt:

Code: Select all

2017.09.01,09:30,247.93,248.48,247.68,247.88,4505292
2017.09.05,09:30,247.24,247.51,244.95,246.09,8576968
2017.09.06,09:30,246.85,247.28,246.23,246.91,5647995 

Antonio

[purpleglow]

That's perfect! Amazing! I added a credit to you Aacini in the batch file as well as a link to this thread.