I made a program in Python that changes the 1-minute data of FX to an arbitrary time frame (1 hour frame, etc.)

About this article

Create a program that changes the 1-minute data of FX to any hourly bar (such as 1-hour bar).

Deliverables

1-minute data ... Change to any time and output to CSV ↓ (The image below is for 1 hour)

Data preparation

The 1-minute data was downloaded from GMO Click Securities. If you have an account, you can download even if the deposit amount is 0. I am very grateful. (* For other companies, if the deposit amount is not a certain amount, it will be useless, or there will be only daily level data.)

This time, we downloaded the US dollar / yen data from January 2007 to September 2020. image.png

Folder structure

When you unzip the downloaded data, it has the following folder structure. / Currency name_yyyymm/yyyymm/

The CSV of each day (currency name_yyyymmdd.csv) is saved in that folder. image.png

The CSV data downloaded this time is I plan to use it in future programs, so I will save it in the following tree structure. image.png

With such a tree structure, it is easy to handle even if the types of currencies to be downloaded in the future increase. Also, the program created this time is in the same hierarchy as the csv folder, I will create a folder called "Make_OHCL" and save it there.

About the contents of CSV

csv has the following field structure in 2007. image.png

However, since around 2016, the number of fields has increased as follows. Spreads (trading fees) are taken into consideration. image.png

It's a bit annoying, but you need to support two types of CSV.

code

The program created this time is in the same hierarchy as the csv folder, Create a folder called "Make_OHCL" and create it directly under it. Data processing uses numpy instead of pandas to speed it up.

from copy import copy
import glob
import numpy as np
import pandas as pd

def make_ohlc(ashi, arr=None):
    """
Function Description: Creates an OHLC for the specified timeframe and returns an array.
    ashi:Timeframe after change. If it is 60, it is 1 hour.
    arr:A 1-minute csv file converted to an array.
    """

    #If there are 6 or more columns of the read CSV file, read only the 1st to 5th columns.
    if arr.shape[1] > 5:
        arr = arr[:,0:5]

    arr = np.c_[arr, np.zeros((len(arr),4))] #4 columns added
    for i in range(0, len(arr), ashi):
        try:
            arr[i,5] = arr[i,1] #Open price
            max_tmp = arr[i:i+ashi,2].astype(np.float) #Get a list of high prices for a specified period
            arr[i,6] = max_tmp.max() #High price
            min_tmp = arr[i:i+ashi,3].astype(np.float) #Get a list of low prices for a specified period
            arr[i,7] = min_tmp.min() #Low price
            arr[i,8] = arr[i+ashi-1,4] #closing price
        except IndexError:
            pass

    arr = np.delete(arr, [1,2,3,4], axis=1) #Delete the 2nd to 5th columns because they are no longer needed
    arr = arr[arr[:,4] != 0] #Delete line 0

    return arr

currency = 'USDJPY' #Currency pair name
ashi = 60 #The length of the foot you want to get(60 minutes for 60 minutes)
arr = None #Initialize arr

csv_dir = '../csv/' + currency + '/' # /csv/Currency name folder
dir_list = glob.glob(csv_dir + '*') # csv/Currency name/Currency name_Get a list of yyyymm folders

for i in range(len(dir_list)):
    file_list = glob.glob(dir_list[i] + '/' + dir_list[i][-6:] + '/*') #Get the path list of csv files

    for j in range(len(file_list)):
        pre_arr = copy(arr) #Pre the previous arr_Evacuate to arr
        csv_arr = np.loadtxt(file_list[j], delimiter=",", skiprows=1, dtype='object') #Load csv into array
        arr = make_ohlc(ashi, csv_arr) #Change foot length

        if pre_arr is not None:
            #Concatenate the previous arr and the converted arr
            arr = np.vstack([pre_arr,arr])


filename = currency + '_ashi=' + str(ashi) + '.csv'
np.savetxt(filename, arr , delimiter="," , header="Date,Open,High,Low,Close" ,fmt="%s") #Save to CSV

This time there were multiple CSV files in multiple directories, so The code became a little longer due to the concatenation process etc. If you already have CSV in one file, just the following code is fine.

def make_ohlc(ashi, arr=None):
    """
Function Description: Creates an OHLC for the specified timeframe and returns an array.
    ashi:Timeframe after change. If it is 60, it is 1 hour.
    arr:A 1-minute csv file converted to an array.
    """

    #If there are 6 or more columns of the read CSV file, read only the 1st to 5th columns.
    if arr.shape[1] > 5:
        arr = arr[:,0:5]

    arr = np.c_[arr, np.zeros((len(arr),4))] #4 columns added
    for i in range(0, len(arr), ashi):
        try:
            arr[i,5] = arr[i,1] #Open price
            max_tmp = arr[i:i+ashi,2].astype(np.float) #Get a list of high prices for a specified period
            arr[i,6] = max_tmp.max() #High price
            min_tmp = arr[i:i+ashi,3].astype(np.float) #Get a list of low prices for a specified period
            arr[i,7] = min_tmp.min() #Low price
            arr[i,8] = arr[i+ashi-1,4] #closing price
        except IndexError:
            pass

    arr = np.delete(arr, [1,2,3,4], axis=1) #Delete the 2nd to 5th columns because they are no longer needed
    arr = arr[arr[:,4] != 0] #Delete line 0

    return arr

currency = 'USDJPY' #Currency pair name
ashi = 60 #The length of the foot you want to get(60 minutes for 60 minutes)

csv_arr = np.loadtxt(<csv file path>, delimiter=",", skiprows=1, dtype='object') #Load csv into array
arr = make_ohlc(ashi, csv_arr) #Change foot length

filename = currency + '_ashi=' + str(ashi) + '.csv'
np.savetxt(filename, arr , delimiter="," , header="Date,Open,High,Low,Close" ,fmt="%s") #Save to CSV

Verification

Confirm that the changed CSV file is output for 1 hour.

If you find it helpful, please click LGTM. It will be encouraging of the update.

The following article introduces how to create a chart image from a CSV file. https://qiita.com/sw1394/items/b2a86cfc663d89915e28

Recommended Posts

I made a program in Python that changes the 1-minute data of FX to an arbitrary time frame (1 hour frame, etc.)
I made a program to check the size of a file in Python
I made a program in Python that reads CSV data of FX and creates a large amount of chart images
I made an appdo command to execute a command in the context of the app
I want to store the result of% time, %% time, etc. in an object (variable)
I made a Python program for Raspberry Pi that operates Omron's environmental sensor in the mode with data storage
I made a program to collect images in tweets that I liked on twitter with Python
I made a program that solves the spot the difference in seconds
A function that measures the processing time of a method in python
[Python] A program that rotates the contents of the list to the left
I made a payroll program in Python!
I made a web application in Python that converts Markdown to HTML
I want to color a part of an Excel string in Python
I want to leave an arbitrary command in the command history of Shell
Python: I want to measure the processing time of a function neatly
I made a function to see the movement of a two-dimensional array (Python)
Let's use Python to represent the frequency of binary data contained in a data frame in a single bar graph.
I made a Caesar cryptographic program in Python.
I made a tool to estimate the execution time of cron (+ PyPI debut)
With LINEBot, I made an app that informs me of the "bus time"
I made a script to record the active window using win32gui of Python
Part 1 I wrote an example of the answer to the reference problem of how to write offline in real time in Python
[Python] A program that counts the number of valleys
I made a prime number generation program in Python 2
[Python] A program that compares the positions of kangaroos.
[Python] I made an app to practice the subtle voice distinction of English words.
A program that summarizes the transaction history csv data of SBI SECURITIES stocks [Python3]
A note that runs an external program in Python and parses the resulting line
[Python / C] I made a device that wirelessly scrolls the screen of a PC remotely.
I made a package to filter time series with python
I made a class to get the analysis result by MeCab in ndarray with python
A program that automatically resizes the iOS app icon to the required image size in Python
Summary of points to keep in mind when writing a program that runs on Python 2.5
I made a function to check the model of DCGAN
[Python] Programming to find the number of a in a character string that repeats a specified number of times.
I tried "a program that removes duplicate statements in Python"
How to calculate the sum or average of time series csv data in an instant
I tried to create a Python script to get the value of a cell in Microsoft Excel
I made a function to crop the image of python openCV, so please use it.
I wrote a doctest in "I tried to simulate the probability of a bingo game with Python"
A Python program that aggregates time usage from icalendar data
An easy way to view the time taken in Python and a smarter way to improve it
[Python] A program to find the number of apples and oranges that can be harvested
I made a Line bot that guesses the gender and age of a person from an image
I want to extract an arbitrary URL from the character string of the html source with python
Put the process to sleep for a certain period of time (seconds) or more in Python
I made a garbled generator that encodes favorite sentences from UTF-8 to Shift-JIS (cp932) in Python
I made a function to check if the webhook is received in Lambda for the time being
[Python] I tried to make a simple program that works on the command line using argparse.
A story that didn't work when I tried to log in with the Python requests module
Part 1 I wrote the answer to the reference problem of how to write offline in real time in Python
I made a program to solve (hint) Saizeriya's spot the difference
How to determine the existence of a selenium element in Python
I compared the calculation time of the moving average written in Python
How to know the internal structure of an object in Python
Movement that changes direction in the coordinate system I tried Python 3
A story that struggled to handle the Python package of PocketSphinx
How to check the memory size of a variable in Python
I tried to make a regular expression of "time" using Python
How to create a heatmap with an arbitrary domain in Python
I wrote the code to write the code of Brainf * ck in python