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);
}
}
//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);
}
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);
}
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);
}
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);
}
}
}
// 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");
}
}