In [1]:
import pandas as pd
import os
import time
from datetime import datetime
from matplotlib.dates import date2num
import sqlite3

In [119]:
# Create NEW SQL database
connect = sqlite3.connect('Yahoo_sqlite.db')
cursor = connect.cursor()


PATH = '/Users/insuyu/GitHub/historical.data/yahoo.csv/'
#PATH = '/Users/insuyu/GitHub/python/11. Sqlite3/'


# For All Files in PATH
nFiles = 0
for filename in os.listdir(PATH):
    # Find only *.csv files
    if filename.endswith(".csv"):
        print ("[%d] %s" % (nFiles, filename))
        # Load CVS to Database
        DB = pd.read_csv(PATH+filename)
    
        # Generate Moving Average Data
        DB['MA20'] = pd.stats.moments.rolling_mean(DB['Adj Close'], 20)
        DB['MA30'] = pd.stats.moments.rolling_mean(DB['Adj Close'], 30)
        #DB['MA30'] = pd.stats.moments.rolling_mean(DB['Adj Close'], 30)
        #DB['MA60'] = pd.stats.moments.rolling_mean(DB['Adj Close'], 60)
        #DB['MA120'] = pd.stats.moments.rolling_mean(DB['Adj Close'], 120)
        DB['MA200']     = pd.stats.moments.rolling_mean(DB['Adj Close'], 200)
        DB['MA240']     = pd.stats.moments.rolling_mean(DB['Adj Close'], 240)
        DB['DateStamp'] = [ date2num(datetime.strptime(date,"%Y-%m-%d")) for date in DB['Date'] ]
        
        ###########################################################################
        Code = filename[:-4] # Trading Code
        
        # Create a New Table for Code
        # DateStamp(REAL) | Date(TEXT) | Open(REAL) | High(REAL) | Low(REAL) 
        #    | Close(REAL) | Volume(REAL) | AdjClose(REAL) | MA20(REAL) | MA30(REAL) | MA200(REAL) | MA240 (REAL)
        command = "CREATE TABLE KS%s(DateStamp INT, Date TEXT, Open REAL, High REAL, Low REAL,  \
                        Close REAL, Volume INT, AdjClose REAL, MA20 REAL, MA30 REAL, MA200 REAL, MA240 REAL)" % Code
        print(command)
        cursor.execute(command)
        
        command = "INSERT INTO KS%s(DateStamp, Date, Open, High, Low, Close, Volume, AdjClose, MA20, MA30, MA200, MA240) \
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?)" % Code
        print(command)
        
        for i in range(len(DB)):
            DateStamp = DB['DateStamp'][i]
            Date = DB['Date'][i]
            Open = DB['Open'][i]
            High = DB['High'][i]
            Low = DB['Low'][i] 
            Close = DB['Close'][i]
            Volume = DB['Volume'][i]
            AdjClose = DB['Adj Close'][i]
            MA20 = DB['MA20'][i]
            MA30 = DB['MA30'][i]
            MA200 = DB['MA200'][i]
            MA240 = DB['MA240'][i]
            
            cursor.execute(command,(DateStamp, Date, Open, High, Low, Close, Volume, AdjClose, MA20, MA30, MA200, MA240) )
            connect.commit()
        nFiles += 1


[0] 000020.csv
CREATE TABLE KS000020(DateStamp INT, Date TEXT, Open REAL, High REAL, Low REAL,                          Close REAL, Volume REAL, AdjClose REAL, MA20 REAL, MA30 REAL, MA200 REAL, MA240 REAL)
INSERT INTO KS000020(DateStamp, Date, Open, High, Low, Close, Volume, AdjClose, MA20, MA30, MA200, MA240)         VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-119-82360804f457> in <module>()
     47             Volume = DB['Volume'][i]
     48             AdjClose = DB['Adj Close'][i]
---> 49             MA20 = DB['MA20'][i]
     50             MA30 = DB['MA30'][i]
     51             MA200 = DB['MA200'][i]

/Users/insuyu/anaconda/lib/python3.4/site-packages/pandas/core/frame.py in __getitem__(self, key)
   1795             return self._getitem_multilevel(key)
   1796         else:
-> 1797             return self._getitem_column(key)
   1798 
   1799     def _getitem_column(self, key):

/Users/insuyu/anaconda/lib/python3.4/site-packages/pandas/core/frame.py in _getitem_column(self, key)
   1802         # get column
   1803         if self.columns.is_unique:
-> 1804             return self._get_item_cache(key)
   1805 
   1806         # duplicate columns & possible reduce dimensionaility

/Users/insuyu/anaconda/lib/python3.4/site-packages/pandas/core/generic.py in _get_item_cache(self, item)
   1082         res = cache.get(item)
   1083         if res is None:
-> 1084             values = self._data.get(item)
   1085             res = self._box_item_values(item, values)
   1086             cache[item] = res

/Users/insuyu/anaconda/lib/python3.4/site-packages/pandas/core/internals.py in get(self, item, fastpath)
   2849 
   2850             if not isnull(item):
-> 2851                 loc = self.items.get_loc(item)
   2852             else:
   2853                 indexer = np.arange(len(self.items))[isnull(self.items)]

/Users/insuyu/anaconda/lib/python3.4/site-packages/pandas/core/index.py in get_loc(self, key, method)
   1570         """
   1571         if method is None:
-> 1572             return self._engine.get_loc(_values_from_object(key))
   1573 
   1574         indexer = self.get_indexer([key], method=method)

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:3824)()

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:3704)()

pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12280)()

pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12231)()

KeyError: 'MA20'

Convert a string "2015-8-1" to 73468.0


In [29]:
PATH = '/Users/insuyu/GitHub/python/12. CSV2SQL/'
# conversion "%Y-%m-%d" string to date number
DB = pd.read_csv(PATH+'003490.csv')
date = DB['Date'][20]
date2num(datetime.strptime(date,"%Y-%m-%d"))


Out[29]:
730151.0

Add DateStamp to DB


In [31]:
DB['MA20'] = pd.stats.moments.rolling_mean(DB['Adj Close'], 20)
DB['DateStamp'] = [ date2num(datetime.strptime(date,"%Y-%m-%d")) for date in DB['Date'] ]
DB


Out[31]:
Date Open High Low Close Volume Adj Close MA30 MA60 MA120 MA240 MA20 DateStamp
0 2000-01-04 10700.02 11800.04 10700.02 11800.04 1810000 14564.59 NaN NaN NaN NaN NaN 730123
1 2000-01-05 11599.98 13099.96 11099.98 13099.96 2130200 16169.06 NaN NaN NaN NaN NaN 730124
2 2000-01-06 13000.01 13000.01 11299.97 11599.98 1499700 14317.66 NaN NaN NaN NaN NaN 730125
3 2000-01-07 12000.02 12250.02 11400.00 12000.02 1283400 14811.42 NaN NaN NaN NaN NaN 730126
4 2000-01-10 11800.04 12200.00 11649.99 11750.02 840700 14502.86 NaN NaN NaN NaN NaN 730129
5 2000-01-11 11599.98 12099.97 11549.96 11800.04 994200 14564.59 NaN NaN NaN NaN NaN 730130
6 2000-01-12 11599.98 11700.01 11099.98 11099.98 710800 13700.52 NaN NaN NaN NaN NaN 730131
7 2000-01-13 10900.00 11500.03 10900.00 11450.01 672100 14132.56 NaN NaN NaN NaN NaN 730132
8 2000-01-14 11899.99 11899.99 11049.97 11150.00 617800 13762.26 NaN NaN NaN NaN NaN 730133
9 2000-01-17 10900.00 11000.03 10299.98 10750.04 775100 13268.59 NaN NaN NaN NaN NaN 730136
10 2000-01-18 10500.04 10700.02 10150.01 10200.03 798300 12589.72 NaN NaN NaN NaN NaN 730137
11 2000-01-19 10100.00 10200.03 9100.01 9200.04 1061500 11355.45 NaN NaN NaN NaN NaN 730138
12 2000-01-20 8799.99 9100.01 8400.03 8590.03 877800 10602.52 NaN NaN NaN NaN NaN 730139
13 2000-01-21 8710.03 9489.98 8400.03 8699.96 1246600 10738.22 NaN NaN NaN NaN NaN 730140
14 2000-01-24 8509.97 8989.99 8509.97 8600.01 1090900 10614.85 NaN NaN NaN NaN NaN 730143
15 2000-01-25 8499.98 8520.04 8300.00 8300.00 641100 10244.55 NaN NaN NaN NaN NaN 730144
16 2000-01-26 8350.02 8720.02 8150.03 8550.00 891500 10553.12 NaN NaN NaN NaN NaN 730145
17 2000-01-27 8619.99 9520.02 8550.00 9450.03 1121600 11664.02 NaN NaN NaN NaN NaN 730146
18 2000-01-28 9499.97 9640.03 9150.02 9340.02 745500 11528.22 NaN NaN NaN NaN NaN 730147
19 2000-01-31 9150.02 9590.01 8900.03 9499.97 742900 11725.65 NaN NaN NaN NaN 12770.5215 730150
20 2000-02-01 9900.01 10650.01 9710.02 10599.99 1593000 13083.39 NaN NaN NaN NaN 12696.4615 730151
21 2000-02-02 10599.99 11150.00 10100.00 10750.04 1205200 13268.59 NaN NaN NaN NaN 12551.4380 730152
22 2000-02-03 10750.04 11000.03 10500.04 10500.04 565700 12960.02 NaN NaN NaN NaN 12483.5560 730153
23 2000-02-04 10500.04 10500.04 10500.04 10500.04 0 12960.02 NaN NaN NaN NaN 12390.9860 730154
24 2000-02-07 10299.98 10900.00 10100.00 10500.04 1116600 12960.02 NaN NaN NaN NaN 12313.8440 730157
25 2000-02-08 10299.98 10700.02 10150.01 10599.99 607500 13083.39 NaN NaN NaN NaN 12239.7840 730158
26 2000-02-09 10500.04 10750.04 10100.00 10150.01 839300 12527.99 NaN NaN NaN NaN 12181.1575 730159
27 2000-02-10 9999.96 9999.96 9030.02 9309.98 993900 11491.15 NaN NaN NaN NaN 12049.0870 730160
28 2000-02-11 9009.96 9499.97 8749.98 8970.01 1106200 11071.54 NaN NaN NaN NaN 11914.5510 730161
29 2000-02-14 8550.00 9299.99 8550.00 8610.00 772800 10627.18 12648.124000 NaN NaN NaN 11782.4805 730164
... ... ... ... ... ... ... ... ... ... ... ... ... ...
4003 2015-06-09 39400.00 40450.00 39350.00 39750.00 608000 39750.00 42530.000000 46153.333333 46097.916667 42082.708333 41307.5000 735758
4004 2015-06-10 39950.00 40000.00 37800.00 38050.00 1023500 38050.00 42231.666667 45997.500000 46019.166667 42098.958333 41007.5000 735759
4005 2015-06-11 38000.00 38750.00 37450.00 38100.00 612800 38100.00 41975.000000 45831.666667 45940.833333 42115.208333 40730.0000 735760
4006 2015-06-12 38600.00 39050.00 37200.00 37500.00 546000 37500.00 41698.333333 45654.166667 45866.250000 42132.916667 40440.0000 735761
4007 2015-06-15 36950.00 36950.00 35150.00 35200.00 817100 35200.00 41351.666667 45454.166667 45769.583333 42138.125000 40120.0000 735764
4008 2015-06-16 35200.00 35850.00 34800.00 35750.00 634700 35750.00 41023.333333 45237.500000 45672.083333 42146.875000 39855.0000 735765
4009 2015-06-17 35750.00 35800.00 34800.00 35600.00 535000 35600.00 40780.000000 45009.166667 45573.333333 42157.291667 39447.5000 735766
4010 2015-06-18 35800.00 36200.00 35350.00 35700.00 509000 35700.00 40513.333333 44809.166667 45475.416667 42163.958333 39052.5000 735767
4011 2015-06-19 36000.00 38150.00 35800.00 37850.00 1011600 37850.00 40295.000000 44645.833333 45414.583333 42177.708333 38747.5000 735768
4012 2015-06-22 38750.00 39700.00 38750.00 39350.00 835800 39350.00 40103.333333 44489.166667 45357.500000 42195.833333 38517.5000 735771
4013 2015-06-23 39350.00 39750.00 38650.00 39700.00 494900 39700.00 39965.000000 44341.666667 45307.916667 42217.291667 38397.5000 735772
4014 2015-06-24 39750.00 40450.00 39300.00 39950.00 418900 39950.00 39828.333333 44162.500000 45278.333333 42238.541667 38332.5000 735773
4015 2015-06-25 39650.00 40250.00 39300.00 39800.00 278700 39800.00 39700.000000 43970.833333 45249.166667 42259.166667 38270.0000 735774
4016 2015-06-26 39800.00 39800.00 38800.00 39100.00 296900 39100.00 39560.000000 43740.833333 45197.500000 42276.875000 38202.5000 735775
4017 2015-06-29 38400.00 38750.00 37750.00 37900.00 380500 37900.00 39436.666667 43489.166667 45139.166667 42288.541667 38155.0000 735778
4018 2015-06-30 37750.00 40600.00 37700.00 40500.00 632700 40500.00 39418.333333 43287.500000 45098.750000 42307.500000 38250.0000 735779
4019 2015-07-01 40600.00 42300.00 39650.00 41850.00 807000 41850.00 39355.000000 43106.666667 45064.166667 42334.166667 38407.5000 735780
4020 2015-07-02 42500.00 43850.00 41900.00 42900.00 1340700 42900.00 39331.666667 42953.333333 45045.416667 42362.916667 38622.5000 735781
4021 2015-07-03 42950.00 43450.00 41450.00 41500.00 583100 41500.00 39250.000000 42773.333333 45019.583333 42391.458333 38747.5000 735782
4022 2015-07-06 40800.00 42600.00 40700.00 41200.00 587800 41200.00 39158.333333 42575.000000 44996.250000 42420.625000 38862.5000 735785
4023 2015-07-07 42600.00 44500.00 42050.00 43550.00 1506500 43550.00 39206.666667 42425.833333 44986.250000 42462.291667 39052.5000 735786
4024 2015-07-08 43550.00 43900.00 41900.00 42000.00 601500 42000.00 39231.666667 42275.833333 44961.250000 42500.625000 39250.0000 735787
4025 2015-07-09 41000.00 41500.00 38950.00 39700.00 1608600 39700.00 39186.666667 42106.666667 44930.833333 42526.875000 39330.0000 735788
4026 2015-07-10 40100.00 40150.00 39500.00 39750.00 512700 39750.00 39163.333333 41952.500000 44895.416667 42555.208333 39442.5000 735789
4027 2015-07-13 40050.00 40550.00 39750.00 40450.00 452200 40450.00 39216.666667 41800.000000 44871.250000 42585.625000 39705.0000 735792
4028 2015-07-14 40700.00 40800.00 39400.00 39450.00 657700 39450.00 39245.000000 41652.500000 44829.583333 42612.500000 39890.0000 735793
4029 2015-07-15 40450.00 40500.00 39400.00 39450.00 886900 39450.00 39270.000000 41510.000000 44788.750000 42637.083333 40082.5000 735794
4030 2015-07-16 39000.00 40300.00 38700.00 39450.00 9033400 39450.00 39298.333333 41344.166667 44728.333333 42661.875000 40270.0000 735795
4031 2015-07-17 39550.00 40000.00 38250.00 39000.00 1149500 39000.00 39298.333333 41206.666667 44657.500000 42684.791667 40327.5000 735796
4032 2015-07-20 39050.00 39400.00 38100.00 38450.00 828900 38450.00 39283.333333 41044.166667 44588.333333 42703.750000 40282.5000 735799

4033 rows × 13 columns

Access SQLite data


In [26]:
connect = sqlite3.connect('Yahoo_sqlite.db')
cursor = connect.cursor()

In [39]:
sql = "SELECT * FROM KR003490 WHERE DateStamp > 735760"

for row in cursor.execute(sql): #, [(735760)]):
    print (row)


(735760, '2015-06-11', 38000.0, 38750.0, 37450.0, 38100.0, 612800, 38100.0, 40729.999999999956, 41975.00000000002, 43590.50000000003, 42115.20833333324)