Exemple #1
0
        private void startTradgTradhUpdating()
        {
            List <string> linkList = GetSourceFileLinks(tse_url);

            nameDic = JapanShared.GetNameMap(baknoteFilePath, workSheetName);
            // For tradgUpdate
            try
            {
                dailySourceFilePath = DownloadFile(linkList[0]);
                JpTRADGInfo tradgTradingInfo = printAndGetJpDailyTradingInfo(dailySourceFilePath);
                GenerateTTradgTargetFile(targretFileDir, tradgTradingInfo);
            }
            catch (Exception ex)
            {
                Logger.Log("There's error during task: TRADG. Exception: " + ex.Message);
            }
            // For TRADH
            try
            {
                if (linkList.Count == 2)
                {
                    weeklySourceFilePath = DownloadFile(linkList[1]);
                    JpTRADHInfo tradhTradeInfo = PrintAndGetJpWeeklyTradingInfo(weeklySourceFilePath);
                    GenerateTradhTargetFile(targretFileDir, tradhTradeInfo);
                }
            }
            catch (Exception ex)
            {
                Logger.Log("There's error during task: TRADH. Exception: " + ex.Message);
            }
        }
Exemple #2
0
        //get all the transactions
        private List <Transaction> GetTransactionList(int startRow, int startCol, Worksheet worksheet)
        {
            List <Transaction> transactionList = new List <Transaction>();
            int currentRow = startRow;
            int currentCol = startCol;

            while (currentRow < startRow + 15)
            {
                Range r = ExcelUtil.GetRange(currentRow, currentCol, worksheet);
                if (r.Value2 != null && r.Value2.ToString().Trim() != string.Empty && r.Value2.ToString().Trim() != "-")
                {
                    string      warningMsg  = string.Empty;
                    Transaction transaction = new Transaction
                    {
                        CompanyInfo =
                        {
                            OriginalName = ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Trim()
                        }
                    };
                    JapanShared.UpdateCompanyInfo(nameDic, transaction.CompanyInfo, out warningMsg);
                    Logger.Log(warningMsg);
                    transaction.SellSum  = ExcelUtil.GetRange(currentRow, 7, worksheet).Value2.ToString().Trim();
                    transaction.BuySum   = ExcelUtil.GetRange(currentRow, 9, worksheet).Value2.ToString().Trim();
                    transaction.TotalSum = ExcelUtil.GetRange(currentRow, 13, worksheet).Value2.ToString().Trim();
                    transactionList.Add(transaction);
                }
                currentRow++;
            }
            return(transactionList);
        }
Exemple #3
0
        private JpTRADGInfo printAndGetJpDailyTradingInfo(string filePath)
        {
            JpTRADGInfo tradgInfo = new JpTRADGInfo();

            using (ExcelApp app = new ExcelApp(false, false))
            {
                var workbook  = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
                var worksheet = workbook.Worksheets[1] as Worksheet;
                JapanShared.PrintWorksheet(worksheet, printerName, XlPageOrientation.xlPortrait);
                tradgInfo.GSellSum       = ExcelUtil.GetRange(7, 5, worksheet).Value2.ToString().Trim();
                tradgInfo.GBuySum        = ExcelUtil.GetRange(7, 10, worksheet).Value2.ToString().Trim();
                tradgInfo.CurrentSellSum = ExcelUtil.GetRange(12, 5, worksheet).Value2.ToString().Trim();
                tradgInfo.NextSellSum    = ExcelUtil.GetRange(12, 6, worksheet).Value2.ToString().Trim();
                tradgInfo.TotalSellSum   = ExcelUtil.GetRange(12, 8, worksheet).Value2.ToString().Trim();
                tradgInfo.CurrentNChg1   = ExcelUtil.GetRange(13, 5, worksheet).Value2.ToString();
                tradgInfo.NextNChg1      = ExcelUtil.GetRange(13, 6, worksheet).Value2.ToString();
                tradgInfo.TotalNchg1     = ExcelUtil.GetRange(13, 8, worksheet).Value2.ToString();
                tradgInfo.CurrentBuySum  = ExcelUtil.GetRange(12, 10, worksheet).Value2.ToString();
                tradgInfo.NextBuySum     = ExcelUtil.GetRange(12, 12, worksheet).Value2.ToString();
                tradgInfo.TotalBuySum    = ExcelUtil.GetRange(12, 14, worksheet).Value2.ToString();
                tradgInfo.CurrentNChg2   = ExcelUtil.GetRange(13, 10, worksheet).Value2.ToString();
                tradgInfo.NextNChg2      = ExcelUtil.GetRange(13, 12, worksheet).Value2.ToString();
                tradgInfo.TotalNchg2     = ExcelUtil.GetRange(13, 14, worksheet).Value2.ToString().Trim();
                tradgInfo.Date           = GetDateTime(ExcelUtil.GetRange(33, 3, worksheet).Value2.ToString().Trim()).ToString("ddMMMyy");
                int currentRow = 37;
                while (ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Trim() != "-")
                {
                    Transaction tran = new Transaction
                    {
                        CompanyInfo =
                        {
                            OriginalName =
                                ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Replace(" ", "")
                        }
                    };
                    if (nameDic.ContainsKey(tran.CompanyInfo.OriginalName))
                    {
                        tran.CompanyInfo.EnglishName      = nameDic[tran.CompanyInfo.OriginalName].EnglistName;
                        tran.CompanyInfo.ShortEnglishName = nameDic[tran.CompanyInfo.OriginalName].ShortName;
                        tran.CompanyInfo.JapaneseName     = nameDic[tran.CompanyInfo.OriginalName].JapaneseName;
                    }
                    else
                    {
                        Logger.Log(string.Format("There's no such name for {0}, please check the baknote file.", tran.CompanyInfo.OriginalName), Logger.LogType.Warning);
                    }
                    tran.SellSum  = ExcelUtil.GetRange(currentRow, 7, worksheet).Value2.ToString();
                    tran.BuySum   = ExcelUtil.GetRange(currentRow, 9, worksheet).Value2.ToString();
                    tran.TotalSum = ExcelUtil.GetRange(currentRow, 13, worksheet).Value2.ToString();
                    tradgInfo.TransactionList.Add(tran);
                    currentRow++;
                }
                tradgInfo.Top15SellSum  = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString().Trim();
                tradgInfo.Top15BuySum   = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString().Trim();
                tradgInfo.Top15TotalSum = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString().Trim();
                tradgInfo.GTotalSum     = ExcelUtil.GetRange(53, 13, worksheet).Value2.ToString().Trim();
                workbook.Close(false, workbook.FullName, Missing.Value);
            }
            return(tradgInfo);
        }
Exemple #4
0
 private DateTime[] ParseToGetTimeDuration(string sourceTimeDuration)
 {
     //平成24年3月5日~3月9日
     string[]   timeDuration = sourceTimeDuration.Split('~');
     DateTime[] dateArr      = { DateTime.Today, DateTime.Today };
     dateArr[0] = JapanShared.TransferJpDate(timeDuration[0]);
     dateArr[1] = JapanShared.TransferJpDate(timeDuration[1]);
     return(dateArr);
 }
        public static void GetOS225OPZData(Workbook book, ref OS225OPZOriginData data)
        {
            if (book == null || data == null)
            {
                return;
            }
            Worksheet sheet = book.Worksheets[1] as Worksheet;

            if (sheet == null)
            {
                return;
            }
            string updateDate = ExcelUtil.GetRange("A2", sheet).Text.ToString();

            data.updateDate = JapanShared.TransferJpDate(updateDate);

            string putTradingDate = ExcelUtil.GetRange("B4", sheet).Text;

            putTradingDate      = putTradingDate.Replace("プット", "");
            data.putTradingDate = JapanShared.TransferJpDate(putTradingDate);

            string callTradingDate = ExcelUtil.GetRange("I4", sheet).Text;

            callTradingDate      = callTradingDate.Replace("コール", "");
            data.callTradingDate = JapanShared.TransferJpDate(callTradingDate);

            int offset = 17;

            for (int i = 0; i < 5; i++)
            {
                string exchangeCount = ExcelUtil.GetRange(6 + i * offset, 1, sheet).Text.ToString();
                exchangeCount = exchangeCount.Replace("(", "").Replace(")", "").Replace("円", "").Replace(",", "").Trim();
                data.putExchangeCount.Add(exchangeCount);

                exchangeCount = ExcelUtil.GetRange(6 + i * offset, 8, sheet).Text.ToString();
                exchangeCount = exchangeCount.Replace("(", "").Replace(")", "").Replace("円", "").Replace(",", "").Trim();
                data.callExchangeCount.Add(exchangeCount);

                for (int j = 0; j < 15; j++)
                {
                    OSETradeCompanyInfo Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 2);
                    data.putInfo.Add(Info);

                    Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 4);
                    data.putInfo.Add(Info);

                    Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 9);
                    data.callInfo.Add(Info);

                    Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 11);
                    data.callInfo.Add(Info);
                }
            }
        }
 protected override void Initialize()
 {
     base.Initialize();
     app = new ExcelApp(false, false);
     if (app.ExcelAppInstance == null)
     {
         Logger.Log("Excel cannot be started", Logger.LogType.Error);
     }
     configObj = Config as JapanOSETradeVolumeUpdatorConfig;
     nameDic   = JapanShared.GetNameMap(configObj.BAKNOTE_FILE_PATH, configObj.WORKSHEETNAME_BAKNOTE);
 }
Exemple #7
0
        public static void GetOSOPT35_46Data(Workbook book, ref OSOPT35_46OriginData data, Dictionary <string, NameMap> nameDic)
        {
            if (book == null || data == null)
            {
                return;
            }
            Worksheet sheet = book.Worksheets[1] as Worksheet;

            if (sheet == null)
            {
                return;
            }

            string updateDate = ExcelUtil.GetRange("A2", sheet).Text.ToString();

            data.updateDate = JapanShared.TransferJpDate(updateDate);

            int offset = 19;

            for (int i = 0; i < 10; i++)
            {
                string count = ExcelUtil.GetRange(6 + i * offset, 3, sheet).Text.ToString();
                data.code.Add(count);
                count = ExcelUtil.GetRange(6 + i * offset, 12, sheet).Text.ToString();
                data.code.Add(count);

                count = GetExchangeCount2(sheet, 7 + i * offset, 7);
                data.exchangeCount2.Add(count);
                count = GetExchangeCount2(sheet, 7 + i * offset, 16);
                data.exchangeCount2.Add(count);

                string tmp = GetPutOrCall(sheet, 6 + i * offset, 7);
                data.putOrCall.Add(tmp);
                tmp = GetPutOrCall(sheet, 6 + i * offset, 16);
                data.putOrCall.Add(tmp);

                OSETradeCompanyInfo exchangeName = GetExchangeName(sheet, 6 + i * offset, 3);
                updateCompanyInfo(exchangeName, nameDic);
                data.exchangeName.Add(exchangeName);
                exchangeName = GetExchangeName(sheet, 6 + i * offset, 12);
                updateCompanyInfo(exchangeName, nameDic);
                data.exchangeName.Add(exchangeName);

                string exchangDate = ExcelUtil.GetRange(6 + i * offset, 6, sheet).Text.ToString();
                data.exchangeDate.Add(JapanShared.TransferJpDate(exchangDate));
                exchangDate = ExcelUtil.GetRange(6 + i * offset, 15, sheet).Text.ToString();
                data.exchangeDate.Add(JapanShared.TransferJpDate(exchangDate));

                for (int j = 9; j <= 23; j++)
                {
                    OSETradeCompanyInfo info = GetTradingInfo(sheet, j + i * offset, 3, 2);
                    updateCompanyInfo(info, nameDic);
                    data.info.Add(info);

                    info = GetTradingInfo(sheet, j + i * offset, 6, 1);
                    updateCompanyInfo(info, nameDic);
                    data.info.Add(info);

                    info = GetTradingInfo(sheet, j + i * offset, 12, 2);
                    updateCompanyInfo(info, nameDic);
                    data.info.Add(info);

                    info = GetTradingInfo(sheet, j + i * offset, 15, 1);
                    updateCompanyInfo(info, nameDic);
                    data.info.Add(info);
                }
            }
        }
Exemple #8
0
        // Print Source File and Get Weekly TradingInfo
        private JpTRADHInfo PrintAndGetJpWeeklyTradingInfo(string filePath)
        {
            JpTRADHInfo tradhInfo = new JpTRADHInfo();

            using (ExcelApp app = new ExcelApp(false, false))
            {
                var       workbook  = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
                Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
                JapanShared.PrintWorksheet(worksheet, printerName, XlPageOrientation.xlPortrait);
                worksheet.UsedRange.UnMerge();
                JpTRADHInfoPart1 part1        = new JpTRADHInfoPart1();
                DateTime[]       part1DateArr = ParseToGetTimeDuration(ExcelUtil.GetRange(33, 3, worksheet).Value2.ToString());
                part1.StartDate       = part1DateArr[0];
                part1.EndDate         = part1DateArr[1];
                part1.TransactionList = GetTransactionList(37, 3, worksheet);
                part1.Top15SellSum    = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString();
                part1.Top15BuySum     = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString();
                part1.Top15TotalSum   = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString();
                part1.GTotalSellSum   = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString();
                part1.GTotalBuySum    = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString();
                part1.GTotalTotalSum  = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString();
                JpTRADHInfoPart2 part2            = new JpTRADHInfoPart2();
                string           part2SourceTitle = ExcelUtil.GetRange(5, 3, worksheet).Value2.ToString().Trim();
                part2.Title = part2SourceTitle.Split('(')[0].Trim();
                DateTime[] part2DateArr = ParseToGetTimeDuration(part2SourceTitle.Split('(')[1].Trim());
                part2.StartDate = part2DateArr[0];
                part2.EndDate   = part2DateArr[1];
                for (int i = 5; i < 16; i++)
                {
                    if (ExcelUtil.GetRange(9, i, worksheet).Value2 != null && ExcelUtil.GetRange(9, i, worksheet).Value2.ToString().Trim() != string.Empty)
                    {
                        part2.ShareSumList.Add(ExcelUtil.GetRange(9, i, worksheet).Value2.ToString().Trim());
                    }
                }
                for (int i = 5; i < 16; i++)
                {
                    if (ExcelUtil.GetRange(10, i, worksheet).Value2 != null && ExcelUtil.GetRange(10, i, worksheet).Value2.ToString().Trim() != string.Empty)
                    {
                        part2.TradeVolList.Add(ExcelUtil.GetRange(10, i, worksheet).Value2.ToString().Trim());
                    }
                }
                JpTRADHInfoPart3 part3            = new JpTRADHInfoPart3();
                string           part3SourceTitle = ExcelUtil.GetRange(12, 3, worksheet).Value2.ToString();
                part3.Title = part3SourceTitle.Split('(')[0].Trim();
                part3.Date  = JapanShared.TransferJpDate(part3SourceTitle.Split('(')[1].Trim());
                for (int i = 5; i < 16; i++)
                {
                    if (ExcelUtil.GetRange(15, i, worksheet).Value2 != null && ExcelUtil.GetRange(15, i, worksheet).Value2.ToString().Trim() != string.Empty)
                    {
                        part3.ShareSumList.Add(ExcelUtil.GetRange(15, i, worksheet).Value2.ToString().Trim());
                    }
                }
                for (int i = 5; i < 16; i++)
                {
                    if (ExcelUtil.GetRange(16, i, worksheet).Value2 != null && ExcelUtil.GetRange(16, i, worksheet).Value2.ToString().Trim() != string.Empty)
                    {
                        part3.ShareIncrementList.Add(ExcelUtil.GetRange(16, i, worksheet).Value2.ToString().Trim());
                    }
                }
                for (int i = 5; i < 16; i++)
                {
                    if (ExcelUtil.GetRange(17, i, worksheet).Value2 != null && ExcelUtil.GetRange(17, i, worksheet).Value2.ToString().Trim() != string.Empty)
                    {
                        part3.TradeVolSumList.Add(ExcelUtil.GetRange(17, i, worksheet).Value2.ToString().Trim());
                    }
                }
                for (int i = 5; i < 16; i++)
                {
                    if (ExcelUtil.GetRange(18, i, worksheet).Value2 != null && ExcelUtil.GetRange(18, i, worksheet).Value2.ToString().Trim() != string.Empty)
                    {
                        part3.TradeVolIncrementList.Add(ExcelUtil.GetRange(18, i, worksheet).Value2.ToString().Trim());
                    }
                }
                tradhInfo.Part1 = part1;
                tradhInfo.Part2 = part2;
                tradhInfo.Part3 = part3;
            }
            return(tradhInfo);
        }
        private void GetOS225FUUData(Workbook book, ref OS225FUUOriginData data)
        {
            if (book == null || data == null)
            {
                return;
            }
            try
            {
                Worksheet sheet = book.Worksheets[1] as Worksheet;
                if (sheet == null)
                {
                    return;
                }
                string updateDate = ExcelUtil.GetRange("A2", sheet).Text.ToString();
                data.updateDate = JapanShared.TransferJpDate(updateDate);

                int lastUsedRow = sheet.UsedRange.Row + sheet.UsedRange.Rows.Count - 1;
                int currentRow  = 1;
                while (currentRow <= lastUsedRow)
                {
                    if (ExcelUtil.GetRange(currentRow, 1, sheet).Value2 != null && ExcelUtil.GetRange(currentRow, 1, sheet).Value2.ToString() != string.Empty)
                    {
                        string text = ExcelUtil.GetRange(currentRow, 1, sheet).Value2.ToString();
                        if (text.Contains("日経225先物"))
                        {
                            string date = ExcelUtil.GetRange(currentRow + 1, 2, sheet).Text.ToString();
                            data.OS225FUU.tradingDate1 = JapanShared.TransferJpDate(date);

                            date = ExcelUtil.GetRange(currentRow + 1, 9, sheet).Text.ToString();
                            data.OS225FUU.tradingDate2 = JapanShared.TransferJpDate(date);

                            for (int i = 0; i < 15; i++)
                            {
                                OSETradeCompanyInfo companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 2);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUU.info1.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 4);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUU.info1.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 9);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUU.info2.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 11);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUU.info2.Add(companyInfo);
                            }
                            currentRow += 20;
                        }
                        else if (text.Contains("日経225mini"))
                        {
                            string date = ExcelUtil.GetRange(currentRow + 1, 2, sheet).Text.ToString();
                            data.OS225FUX.tradingDate1 = JapanShared.TransferJpDate(date);

                            date = ExcelUtil.GetRange(currentRow + 1, 9, sheet).Text.ToString();
                            data.OS225FUX.tradingDate2 = JapanShared.TransferJpDate(date);

                            for (int i = 0; i < 15; i++)
                            {
                                OSETradeCompanyInfo companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 2);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUX.info1.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 4);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUX.info1.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 9);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUX.info2.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 11);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OS225FUX.info2.Add(companyInfo);
                            }
                            currentRow += 20;
                        }

                        else if (text.Contains("TOPIX先物"))
                        {
                            string date = ExcelUtil.GetRange(currentRow + 1, 2, sheet).Text.ToString();
                            data.OSTRADF.tradingDate1 = JapanShared.TransferJpDate(date);

                            date = ExcelUtil.GetRange(currentRow + 1, 9, sheet).Text.ToString();
                            data.OSTRADF.tradingDate2 = JapanShared.TransferJpDate(date);

                            for (int i = 0; i < 15; i++)
                            {
                                OSETradeCompanyInfo companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 2);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OSTRADF.info1.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 4);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OSTRADF.info1.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 9);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OSTRADF.info2.Add(companyInfo);

                                companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 11);
                                updateCompanyInfo(companyInfo, nameDic);
                                data.OSTRADF.info2.Add(companyInfo);
                            }
                            currentRow += 20;
                        }
                        else
                        {
                            currentRow++;
                        }
                    }
                    else
                    {
                        currentRow++;
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Log("GetOS225FUUData failed" + ex.Message);
                LogMessage("GetOS225FUUData failed");
            }
        }