private Opt10001VO getOpt10001VO(string sTrCode, string sRQName)
{
Opt10001VO opt10001VO = new Opt10001VO();
opt10001VO.종목코드 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "종목코드").Trim();
opt10001VO.종목명 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "종목명").Trim();
opt10001VO.결산월 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "결산월").Trim();
opt10001VO.액면가 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "액면가").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "액면가").Trim());
opt10001VO.자본금 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "자본금").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "자본금").Trim());
opt10001VO.상장주식 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "상장주식").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "상장주식").Trim());
opt10001VO.신용비율 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "신용비율").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "신용비율").Trim());
opt10001VO.연중최고 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "연중최고").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "연중최고").Trim());
opt10001VO.연중최저 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "연중최저").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "연중최저").Trim());
opt10001VO.시가총액 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "시가총액").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "시가총액").Trim());
opt10001VO.시가총액비중 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "시가총액비중").Trim();
opt10001VO.외인소진률 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "외인소진률").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "외인소진률").Trim());
opt10001VO.대용가 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "대용가").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "대용가").Trim());
opt10001VO.PER = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "PER").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "PER").Trim());
opt10001VO.EPS = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "EPS").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "EPS").Trim());
opt10001VO.ROE = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "ROE").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "ROE").Trim());
opt10001VO.PBR = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "PBR").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "PBR").Trim());
opt10001VO.EV = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "EV").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "EV").Trim());
opt10001VO.BPS = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "BPS").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "BPS").Trim());
opt10001VO.매출액 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "매출액").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "매출액").Trim());
opt10001VO.영업이익 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "영업이익").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "영업이익").Trim());
opt10001VO.당기순이익 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "당기순이익").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "당기순이익").Trim());
opt10001VO.최고250 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최고").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최고").Trim());
opt10001VO.최저250 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최저").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최저").Trim());
opt10001VO.시가 = Math.Abs(int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "시가").Trim()));
opt10001VO.고가 = Math.Abs(int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "고가").Trim()));
opt10001VO.저가 = Math.Abs(int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "저가").Trim()));
opt10001VO.상한가 = Math.Abs(int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "상한가").Trim()));
opt10001VO.하한가 = Math.Abs(int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "하한가").Trim()));
opt10001VO.기준가 = int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "기준가").Trim());
opt10001VO.예상체결가 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "예상체결가").Trim();
opt10001VO.예상체결수량 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "예상체결수량").Trim();
opt10001VO.최고가일250 = DateTime.ParseExact(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최고가일").Trim(), "yyyyMMdd", null);
opt10001VO.최고가대비율250 = float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최고가대비율").Trim());
opt10001VO.최저가일250 = DateTime.ParseExact(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최저가일").Trim(), "yyyyMMdd", null);
opt10001VO.최저가대비율250 = float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "250최저가대비율").Trim());
opt10001VO.현재가 = Math.Abs(int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "현재가").Trim()));
opt10001VO.대비기호 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "대비기호").Trim();
opt10001VO.전일대비 = int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "전일대비").Trim());
opt10001VO.등락율 = float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "등락율").Trim());
opt10001VO.거래량 = int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "거래량").Trim());
opt10001VO.거래대비 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "거래대비").Trim();
opt10001VO.액면가단위 = axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "액면가단위").Trim();
opt10001VO.통주식 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "유통주식").Trim()) ? 0 : int.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "유통주식").Trim());
opt10001VO.통비율 = System.String.IsNullOrEmpty(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "유통비율").Trim()) ? 0 : float.Parse(axKHOpenAPI1.GetCommData(sTrCode, sRQName, 0, "유통비율").Trim());
return(opt10001VO);
}
private void axKHOpenAPI1_OnReceiveTrData(object sender, AxKHOpenAPILib._DKHOpenAPIEvents_OnReceiveTrDataEvent e)
{
logger.Debug("axKHOpenAPI1_OnReceiveTrData");
if (e.sRQName.Contains("주식기본정보요청_"))
{
Opt10001VO opt10001VO = getOpt10001VO(e.sTrCode, e.sRQName);
logger.Debug(e.sTrCode);
logger.Debug(e.sRQName);
string conditionName = e.sRQName.Split('_')[2];
db.saveItemInfo(opt10001VO, conditionName);
googleSheet.updateCodeListToGoogleSheet(opt10001VO, conditionName);
}
}
public void updateCodeListToGoogleSheet(Opt10001VO opt10001VO, string conditionName)
{
var gsh = new GoogleSheetsHelper("swing-293507-ca9c2651b2d1.json", "13I2pgJbXZjuqX8EbI3WnNCCuK8j3HpFNbqyAQypEYYg");
var row = new GoogleSheetRow();
var cells = new List <GoogleSheetCell>();
cells.Add(new GoogleSheetCell()
{
CellValue = conditionName
});
cells.Add(new GoogleSheetCell()
{
CellValue = DateTime.Now.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.종목명
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.종목코드
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.시가총액.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.통주식.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.시가.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.고가.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.저가.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.현재가.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.등락율.ToString()
});
cells.Add(new GoogleSheetCell()
{
CellValue = opt10001VO.거래량.ToString()
});
row.Cells.AddRange(cells);
var rows = new List <GoogleSheetRow>()
{
row
};
gsh.AddCells(new GoogleSheetParameters()
{
SheetName = sheetName, RangeColumnStart = 1, RangeRowStart = rangeRowStart
}, rows);
rangeRowStart++;
logger.Info($"{opt10001VO.종목명} 업데이트 완료 ({rangeRowStart})");
}
//public IDbConnection getOpenConnection()
//{
// return this.db;
//}
public void saveItemInfo(Opt10001VO opt10001VO, string conditionName)
{
string query = "INSERT INTO stockdb.searched_items" +
"(" +
"condition_name," +
"item_code," +
"item_name," +
"settlement_month," +
"face_value," +
"capital," +
"num_of_listed_shares," +
"credit_ratio," +
"highest_year_round," +
"lowest_year_round," +
"market_capitalization," +
"external_burnout_rate," +
"substitute_stock," +
"per," +
"eps," +
"roe," +
"pbr," +
"bps," +
"take," +
"operating_profit," +
"net_income," +
"highest_250," +
"lowest_250," +
"staring_price," +
"highest_price," +
"lowest_price," +
"upper_limit," +
"lower_limit," +
"reference_price," +
"highest_250_at," +
"highest_250_ratio," +
"lowest_250_at," +
"lowest_250_ratio," +
"present_price," +
"price_state_flag," +
"price_by_prev_day," +
"fluctuation_rate," +
"volume," +
"volume_by_prev_day," +
"stocks_in_circulation," +
"circulation_ratio," +
"created_at)" +
" VALUES (" +
$"'{conditionName}'," +
$"'{opt10001VO.종목코드}'," +
$"'{opt10001VO.종목명}'," +
$"'{opt10001VO.결산월}'," +
$"{opt10001VO.액면가}," +
$"{opt10001VO.자본금}," +
$"{opt10001VO.상장주식}," +
$"{opt10001VO.신용비율}," +
$"{opt10001VO.연중최고}," +
$"{opt10001VO.연중최저}," +
$"{opt10001VO.시가총액}," +
$"{opt10001VO.외인소진률}," +
$"{opt10001VO.대용가}," +
$"{opt10001VO.PER}," +
$"{opt10001VO.EPS}," +
$"{opt10001VO.ROE}," +
$"{opt10001VO.PBR}," +
$"{opt10001VO.BPS}," +
$"{opt10001VO.매출액}," +
$"{opt10001VO.영업이익}," +
$"{opt10001VO.당기순이익}," +
$"{opt10001VO.최고250}," +
$"{opt10001VO.최저250}," +
$"{opt10001VO.시가}," +
$"{opt10001VO.고가}," +
$"{opt10001VO.저가}," +
$"{opt10001VO.상한가}," +
$"{opt10001VO.하한가}," +
$"{opt10001VO.기준가}," +
$"STR_TO_DATE('{opt10001VO.최고가일250.ToString("yyyyMMdd")}','%Y%m%d')," +
$"{opt10001VO.최고가대비율250}," +
$"STR_TO_DATE('{opt10001VO.최저가일250.ToString("yyyyMMdd")}','%Y%m%d')," +
$"{opt10001VO.최저가대비율250}," +
$"{opt10001VO.현재가}," +
$"{opt10001VO.대비기호}," +
$"{opt10001VO.전일대비}," +
$"{opt10001VO.등락율}," +
$"{opt10001VO.거래량}," +
$"{opt10001VO.거래대비}," +
$"{opt10001VO.유통주식}," +
$"{opt10001VO.유통비율}," +
$"STR_TO_DATE('{DateTime.Now.ToString("yyyyMMdd")}','%Y%m%d')" +
")";
logger.Debug(query);
MySqlCommand cmd = new MySqlCommand(query, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message.ToString());
}
finally
{
cmd.Dispose();
}
}
