C# - 키움증권 API를 이용한 실시간 체결 분석 프로그램 개발일지 - 실시간 체결정보 DB에 넣기

마스터욱 0 257 0 0 2017-01-19 00:39:35
데이터베이스는 Access DB 를 사용했습니다.
SQL Lite 를 사용하려다가 설치가 좀 복잡해서, 걍 닷넷에서 기본으로 제공하는 Access DB 로 만들었습니다.
일단 실시간 체결정보를 땡겨오는
"axKHOpenAPI1_OnReceiveRealData" 함수가 핵심입니다.
1시간만 넣었는데도 2Mbyte 공간을 차지하더군요...
다음차례는 이제 쌓여진 체결정보를 바탕으로 체결정보 분석 알고리즘을 적용해 보겠습니다.
C# - 키움증권 API를 이용한 실시간 체결 분석 프로그램 개발일지 - 실시간 체결정보 DB에 넣기
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;
 
namespace Auto_Stock
{
    public partial class MainForm : Form
    {
        private string LoginName;
        private string LoginId;
 
        private string accFileName = "stock_db.accdb";
        private string dbPasswd = "admin";
 
        public MainForm()
        {
            InitializeComponent();
 
            /*
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand connCmd = new OleDbCommand();
            Console.WriteLine("mainform start");
            try
            {
                Console.WriteLine("db access start");
                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + this.accFileName + ";Mode=ReadWrite;Jet OLEDB:Database Password=" + this.dbPasswd;
                conn.Open();
                connCmd.Connection = conn;
                connCmd.CommandText = "INSERT INTO favorite(usr_id) VALUES(@usr_id)";
                connCmd.Parameters.Add(new OleDbParameter("@usr_id", "test"));
                connCmd.ExecuteNonQuery();    // 입력
                connCmd.CommandText = "SELECT * FROM favorite";
                OleDbDataReader oleData = connCmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (oleData.Read())
                {
                    Console.WriteLine(oleData["userId"].ToString());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            */
        }
 
        private void database_proc(string sql, Dictionary<string, string> param)
        {
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand connCmd = new OleDbCommand();
            try
            {
                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + accFileName + ";Mode=ReadWrite;Jet OLEDB:Database Password=" + dbPasswd;
                conn.Open();
                connCmd.Connection = conn;
 
                //connCmd.CommandText = "INSERT INTO favorite(usr_id) VALUES(@usr_id)";
                connCmd.CommandText = sql;
                foreach (KeyValuePair<string, string> row in param)
                {
                    //connCmd.Parameters.Add(new OleDbParameter("@usr_id", "test"));
                    connCmd.Parameters.Add(new OleDbParameter(row.Key, row.Value));
                }
                connCmd.ExecuteNonQuery();    // 입력
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception 에러발생 : " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
 
        private Dictionary<int, Dictionary<string, string>> database_select(string sql, Dictionary<int, string> columns)
        {
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand connCmd = new OleDbCommand();
            Dictionary<int, Dictionary<string, string>> list = new Dictionary<int, Dictionary<string, string>>();
            try
            {
                Console.WriteLine("select start");
                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + this.accFileName + ";Mode=ReadWrite;Jet OLEDB:Database Password=" + this.dbPasswd;
                conn.Open();
                connCmd.Connection = conn;
 
                //connCmd.CommandText = "SELECT * FROM favorite";
                connCmd.CommandText = sql;
                OleDbDataReader oleData = connCmd.ExecuteReader(CommandBehavior.CloseConnection);
                int i = 0;
                while (oleData.Read())
                {
                    //Console.WriteLine("usr_id = " + oleData["usr_id"].ToString());
                    Dictionary<string, string> list_row = new Dictionary<string, string>();
                    foreach(KeyValuePair<int, string> row in columns)
                    {
                        string column = row.Value;
                        list_row[column] = oleData[column].ToString();
                    }
                    list[i++] = list_row;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
 
            return list;
        }
 
        //로그인 버튼 클릭
        private void ButtonLogin_Click(object sender, EventArgs e)
        {
            if(LoginName != null)
            {
                MessageBox.Show("이미 로그인 하셨습니다.");
                return;
            }
 
            long Result;
            Result = axKHOpenAPI1.CommConnect();
            if (Result != 0)
            {
                Console.WriteLine("로그인창 열기 실패!");
            }
            else
            {
                Console.WriteLine("로그인창 열기 성공!");
            }
        }
 
        //로그인 후 개인정보 가져오기
        private void axKHOpenAPI1_OnEventConnect(object sender, AxKHOpenAPILib._DKHOpenAPIEvents_OnEventConnectEvent e)
        {
            Console.WriteLine("개인정보 가져오기 성공!");
            string UserName = axKHOpenAPI1.GetLoginInfo("USER_NAME");
            string UserId = axKHOpenAPI1.GetLoginInfo("USER_ID");
            //ButtonLogin.Text = UserName + "(" + axKHOpenAPI1.GetLoginInfo("USER_ID") +  ")";
            ButtonLogin.Text = axKHOpenAPI1.GetLoginInfo("USER_ID");
            LoginName = UserName;
            LoginId = UserId;
        }
 
        //전체종목리스트에서 주식 row를 클릭하면 실행
        private void listBoxStock_SelectedIndexChanged(object sender, EventArgs e)
        {
        }
 
        //종목리스트 불러오기
        bool IsListCall = false;
        bool 전체종목리스트_초기체크 = true;
        Dictionary<string, string> CodeToString = new Dictionary<string, string>();
        private void ButtonListCall_Click(object sender, EventArgs e)
        {
            if (LoginName == null)
            {
                MessageBox.Show("로그인이 필요합니다.");
            }
            else
            {
                if(IsListCall == true)
                {
                    //MessageBox.Show("이미 호출된 상태입니다.");
                    //return;
                }
 
                Dictionary<string, string> favorite_list = listVIew1_select();
                //Console.WriteLine(favorite_list);
 
                IsListCall = true;
 
                전체종목리스트_초기체크 = true;
                listView1.Items.Clear();
                ListViewStock.Items.Clear();
                ListViewStock.CheckBoxes = true; // 항목옆에 확인란을 표시할지 여부를 선택합니다.
                //ListViewStock.Click += new EventHandler(ListViewCheckClick);
                //ListViewStock.OwnerDraw = true;
 
                //GetCodeListByMarket 메소드는 메소드의 인자로 시장 구분 코드를 문자열로 넘겨주면 메소드의 리턴 값으로 해당 시장에 속해 있는 종목들의 종목 코드 목록을 리턴
                //sMarket – 0:장내, 3:ELW, 4:뮤추얼펀드, 5:신주인수권, 6:리츠, 8:ETF, 9:하이일드펀드, 10:코스닥, 30:제3시장
                Console.WriteLine("종목 불러오기 시작");
                string[] marketList = { "0", "10" };
                int InCnt = 0;
                int OutCnt = 0;
                int i = 0;
                foreach (string MNumber in marketList)
                {
                    string result = axKHOpenAPI1.GetCodeListByMarket(MNumber);
                    string[] stockList = result.Split(';');
 
                    if (MNumber == "0")
                    {
                        InCnt = stockList.Count();
                    }
                    else
                    {
                        OutCnt = stockList.Count();
                    }
 
                    int listView1_cnt = 0;
                    foreach (string code in stockList)
                    {
                        if (code != "")
                        {
                            string StockName = axKHOpenAPI1.GetMasterCodeName(code);
 
                            CodeToString[code] = StockName;
 
                            //전체종목리스트 ListView row 만들자
                            ListViewItem item = new ListViewItem("", 0);
                            item.Checked = false; // 체크박스는 일단 false
                            if (favorite_list.ContainsKey(code))
                            {
                                item.Checked = true;
                                listView1_add(listView1_cnt.ToString(), code, favorite_list[code]); //관심종목추가
                                listView1_cnt++;
 
                                axKHOpenAPI1.SetRealReg("0120", code, "20;15", "1");
                            }
                            else
                            {
                                item.Checked = false;
                            }
 
                            //SubItem
                            item.SubItems.Add(i.ToString());
                            item.SubItems.Add(StockName);
                            item.SubItems.Add(code);
 
                            ListViewStock.Items.Add(item);
                            i++;
                        }
                    }
                }
 
                전체종목리스트_초기체크 = false;
 
                //axKHOpenAPI1.SetInputValue("종목코드", 000030.ToString());
                //axKHOpenAPI1.CommRqData("000030_주식기본정보", "opt10001", 0, "0101");
                //axKHOpenAPI1.SetRealReg("0120", "000030", "9001;302;10;11;25;12;13", "1");  // 000030 종목을 실시간 추가등록
            }
        }
 
        //ListView의 이벤트는 [디자인]창에서 속성모드에서 function 명을 추가해서 사용하자.
        //[전체종목리스트] 체크박스 클릭시 발생
        private void ListViewStock_ItemChecked(object sender, ItemCheckedEventArgs e)
        {
            //Console.WriteLine("ListViewStock_ItemChecked");
            //Console.WriteLine(e.Item.Checked); //true or false
            if(전체종목리스트_초기체크 == false) //초기로딩시에는 실행이 되면 안됨!
            {
                //Console.WriteLine(e.Item.SubItems.Count); //라인의 컬럼개수(4개)
                //Console.WriteLine(e.Item.Index); //라인 row index 번호
                //Console.WriteLine(e.Item.SubItems[2].Text); //유한양행
                if (e.Item.Checked == true)
                {
                    string sql = "INSERT INTO favorite(usr_id, stock_code, stock_name) VALUES(@usr_id, @stock_code, @stock_name)";
                    Dictionary<string, string> param = new Dictionary<string, string>();
                    param["@usr_id"] = LoginId;
                    param["@stock_code"] = e.Item.SubItems[3].Text; //000060
                    param["@stock_name"] = e.Item.SubItems[2].Text; //하이트진로
                    database_proc(sql, param);
                }
                else
                {
                    string sql = "DELETE FROM favorite WHERE usr_id = @usr_id AND stock_code = @stock_code";
                    Dictionary<string, string> param = new Dictionary<string, string>();
                    param["@usr_id"] = LoginId;
                    param["@stock_code"] = e.Item.SubItems[3].Text; //000060
                    database_proc(sql, param);
                }
 
                listView1_loading();
            }
        }
 
        private Dictionary<string, string> listVIew1_select()
        {
            Dictionary<int, string> columns = new Dictionary<int, string>();
            int ci = 0;
            columns[ci++] = "stock_code";
            columns[ci++] = "stock_name";
            Dictionary<int, Dictionary<string, string>> list = database_select("SELECT * FROM favorite WHERE usr_id = '" + LoginId + "'", columns);
            Dictionary<string, string> favorite_list = new Dictionary<string, string>();
            foreach (KeyValuePair<int, Dictionary<string, string>> row in list)
            {
                Console.WriteLine(row.Value["stock_code"] + " => " + row.Value["stock_name"]);
                favorite_list[row.Value["stock_code"]] = row.Value["stock_name"];
            }
 
            return favorite_list;
        }
 
        private void listView1_loading()
        {
            listView1.Items.Clear();
            Dictionary<string, string> favorite_list = listVIew1_select();
            int i = 0;
            foreach (KeyValuePair<string, string> row in favorite_list)
            {
                string number = i.ToString();
                listView1_add(number, row.Key, row.Value);
                i++;
            }
        }
 
        //관심종목 추가
        private void listView1_add(string number, string code, string codeString)
        {
            ListViewItem item2 = new ListViewItem(number, 0);
            item2.SubItems.Add(code);
            item2.SubItems.Add(codeString);
 
            listView1.Items.Add(item2);
        }
 
        //실시간 시세 이벤트라는데 뭔지 잘 몰겠다.
        //이게 보니깐 주기적으로 호출이 된다... 아마도 특정초단위로 알아서 호출되는 건가보다...
        private void axKHOpenAPI1_OnReceiveRealData(object sender, AxKHOpenAPILib._DKHOpenAPIEvents_OnReceiveRealDataEvent e)
        {
            //Console.WriteLine("sRealKey : " + e.sRealKey);
            //Console.WriteLine("sRealType : " + e.sRealType);
            //Console.WriteLine("sRealData : " + e.sRealData);
 
            //e.sRealKey = 종목코드
            //e.sRealType = 리얼타입 (주식시세, 주식체결 등)
            //e.sRealData = 실시간 데이터 전문
 
            if (e.sRealType == "주식체결")
            {
                string 현재가 = axKHOpenAPI1.GetCommRealData(e.sRealKey, 10);
                string 체결량 = axKHOpenAPI1.GetCommRealData(e.sRealKey, 15);
                string 체결시간 = axKHOpenAPI1.GetCommRealData(e.sRealKey, 20);
 
                Console.WriteLine("=======================" + e.sRealKey + "==================================");
                Console.WriteLine("현재가 : " + 현재가);
                Console.WriteLine("체결량 : " + 체결량);
                Console.WriteLine("체결시간 : " + 체결시간);
 
                /*
                 * =======================000030==================================
                 * 현재가   : +12800
                 * 체결량   : +116
                 * 체결시간 : 121850
                 * 
                 * stock_sign 테이블 스키마
                 * idx, code, code_name, sign_price, sign_cnt, sign_mktime, sign_YmdHis, sign_Ymd
                 */
 
                string sql = "";
                sql += "INSERT INTO stock_sign(code, code_name, sign_price, sign_cnt, sign_mktime, sign_YmdHis, sign_Ymd)";
                sql += "VALUES(@code, @code_name, @sign_price, @sign_cnt, @sign_mktime, @sign_YmdHis, @sign_Ymd)";
                Dictionary<string, string> param = new Dictionary<string, string>();
                param["@code"]          = e.sRealKey; //종목코드번호
                param["@code_name"]     = CodeToString[e.sRealKey];
                param["@sign_price"]    = 현재가;
                param["@sign_cnt"]      = 체결량;
                param["@sign_mktime"]   = GetMktime(체결시간).ToString();
                param["@sign_YmdHis"]   = DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + " " + 체결시간;
                param["@sign_Ymd"]      = DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd");
                database_proc(sql, param);
            }
        }
 
        //분석하기 버튼클릭
        //stock[코드번호][거래량] = stock_sign 배열
        Dictionary<string, Dictionary<string, ArrayList>> stock = new Dictionary<string, Dictionary<string, ArrayList>>();
        private void btn_ana_Click(object sender, EventArgs e)
        {
            Console.WriteLine("데이터뽑기 시작");
 
            Dictionary<int, string> columns = new Dictionary<int, string>();
            int ci = 0;
            columns[ci++] = "code";
            columns[ci++] = "code_name";
            columns[ci++] = "sign_price";   //현재가
            columns[ci++] = "sign_cnt";     //체결량
            columns[ci++] = "sign_mktime";  //체결_mktime
            columns[ci++] = "sign_YmdHis";
            columns[ci++] = "sign_Ymd";
            //string sign_Ymd = DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd");
            string sign_Ymd = "20170118"; //일단 테스트를 위해
            Dictionary<int, Dictionary<string, string>> list = database_select("SELECT * FROM stock_sign WHERE sign_Ymd = '" + sign_Ymd + "' ORDER BY idx ASC", columns);
            Dictionary<string, string> stock_list = new Dictionary<string, string>();
            foreach (KeyValuePair<int, Dictionary<string, string>> row in list)
            {
                if(!stock.ContainsKey(row.Value["code"]))
                {
                    stock[row.Value["code"]] = new Dictionary<string, ArrayList>();
                }
 
                if(!stock[row.Value["code"]].ContainsKey(row.Value["sign_cnt"]))
                {
                    stock[row.Value["code"]][row.Value["sign_cnt"]] = new ArrayList();
                }
 
                stock[row.Value["code"]][row.Value["sign_cnt"]].Add(row.Value);
            }
 
            Console.WriteLine("데이터뽑기 종료");
 
            /*
            //자료 잘 들어갔는지 확인
            foreach(Dictionary value in stock["000030"]["-5"])
            {
                string test = value["sign_YmdHis"];
                Console.WriteLine(test);
            }
            */
 
            foreach(KeyValuePair<string, Dictionary<string, ArrayList>> row in stock)
            {
                string 종목코드 = row.Key;
                foreach(KeyValuePair<string, ArrayList> row2 in row.Value)
                {
                    string 체결량 = row2.Key;
                    ArrayList 체결량_리스트1 = new ArrayList(row2.Value);
                    ArrayList 체결량_리스트2 = new ArrayList(row2.Value);
 
                    //하나하나씩 비교하자.
                    foreach(Dictionary<string, string> value1 in 체결량_리스트1)
                    {
                        string value1_mktime = value1["sign_mktime"];
                        체결량_리스트2.RemoveAt(0);
                        foreach (Dictionary<string, string> value2 in 체결량_리스트2)
                        {
                            string value2_mktime = value2["sign_mktime"];
                            int 갭 = int.Parse(value2_mktime) - int.Parse(value1_mktime);
                        }
                    }
                }
            }
 
            /*
                시총(시가총액)
 
                1000억에 100만원을 기준으로 = 0.00001(시가총액 * 0.00001) 금액을 마지막에 빼는거임.
                100 - 분당 규칙패턴 = 순위가 가장 높은순으로
                2분 간격으로 5번 반복하고 가격이 100만원 이고 10개씩 구매했다면 = 1000만원이 2분 간격으로 5번 반복하는 거임
                그러면 1분에 500만원을 사게 된다는 규칙이 성립되는 것이고
                .....
                ....
                ...
                ..
                .
                계속 반복패턴 찾아서 그 분당 금액을 모두 더한다...
                1분당 100
                1분당 40
                뭐 이렇게 쌓이다가.... 토탈이 900이 나왔으면 900 - 100(0.001) = 800 이 되는거임... 
 
 
                500 - 100(0.001) = 400(순위반영)이 되는거임
 
                기준은 반복 5회이상....
 
 
 
 
                특정시간을 정해서 하면 좋을거 같은데???
                장내,코스닥만???
 
 
                1. 클릭 -> 분당금액 리스트 보여주고... 총합계보여주고 총합계 - (시가총액 * 0.001) => 금액
                2. 
                ....
                ...
                10.
 
 
                분단위 규칙성 합계금액
                ---------------------- *  100 => 제일 높은거
                  시가총액 * 0.00001  
             */
        }
 
        ///////////////////////////////////////////////////////// 자체제작 라이브러리
 
        //타임스템프 => MKTIME
        static DateTime ConvertFromUnixTimestamp(double timestamp)
        {
            DateTime origin = new DateTime(1970, 1, 1, 9, 0, 0, 0);
            return origin.AddSeconds(timestamp);
        }
 
        //MKTIME => HHiiss
        static string TimestampToHis(int mktime)
        {
            // 2016-10-10 오후 2:46:16 => 14:46:16
            // 타임 스템프 값을 hhiiss 값으로 변경하기...
            DateTime test = ConvertFromUnixTimestamp(mktime);
            string test2 = test.ToString();
            string[] test3 = test2.Split(' ');
            string[] test4 = test3[2].Split(':');
            string test5 = "";
            int i = 0;
            foreach (string str in test4)
            {
                string ttt = "";
                if (test3[1] == "오후" && i == 0)
                {
                    ttt = (int.Parse(str) + 12).ToString();
                }
                else
                {
                    ttt = str;
                }
 
                string str2 = Regex.Replace(ttt, @"d+", n => n.Value.PadLeft(2, '0'));
                string add = "";
                if (i < 2)
                {
                    add = ":";
                }
                test5 += str2 + add;
                i++;
            }
 
            return test5;
        }
 
        static int GetMktime(string time_data)
        {
            int year = int.Parse(DateTime.Now.ToString("yyyy"));
            int month = int.Parse(DateTime.Now.ToString("MM"));
            int day = int.Parse(DateTime.Now.ToString("dd"));
            int hour = int.Parse(time_data.ToString().Substring(0, 2));
            int minute = int.Parse(time_data.ToString().Substring(2, 2));
            int second = int.Parse(time_data.ToString().Substring(4, 2));
 
            if (hour < 9)
            {
                return 999999;
            }
 
            DateTime dt = new DateTime(year, month, day, hour - 9, minute, second);
 
            int time;
            try
            {
                time = ConvertDatetimeToInt(dt);
            }
            catch (System.IndexOutOfRangeException e)
            {
                time = 0;
            }
 
            return time;
        }
 
        public static string TimeShow(string second)
        {
            int second_int = int.Parse(second);
            string return_minute = Regex.Replace((second_int / 60).ToString(), @"d+", n => n.Value.PadLeft(2, '0'));
            string return_second = Regex.Replace((second_int % 60).ToString(), @"d+", n => n.Value.PadLeft(2, '0'));
 
            return return_minute + ":" + return_second;
        }
 
        public static int ConvertDatetimeToInt(System.DateTime dt)
        {
            System.TimeSpan ts = dt.Subtract(new System.DateTime(1970, 1, 1));
            return (int)(ts.Ticks / 10000000L);
        }
    }
}
Colored by Color Scripter