// Заполняем лист с портфелем Тинькофф
private void ribbon_btnTinkoffClicked()
{
if ((this.Application != null) && (this.Application.ActiveWorkbook != null))
{
// Проверяем наличие нужного листа
Excel.Worksheet TinkoffSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelTinkoffName, sh => { });;
if (TinkoffSheet == null)
{
// лист не найден - добавляем
TinkoffSheet = (Excel.Worksheet) this.Application.ActiveWorkbook.Sheets.Add();
TinkoffSheet.Name = ExcelTinkoffName;
}
if (TinkoffSheet != null)
{
this.Application.StatusBar = "Загружаем курсы валют";
LoadValuteCurs().GetAwaiter().GetResult();
this.Application.StatusBar = "Загружаем список ценных бумаг из портфеля Тинькофф";
LoadTinkoff().GetAwaiter().GetResult();
this.Application.StatusBar = $"Заполняем портфель Тинькофф на листе {ExcelTinkoffName}";
// заполняем лист
int _count = 0;
TinkoffSheet.Activate();
TinkoffSheet.Cells.ClearContents();
// заголовок
_count++;
TinkoffSheet.Cells[_count, 1] = "Счет";
TinkoffSheet.Cells[_count, 2] = "Тикер";
TinkoffSheet.Cells[_count, 3] = "Ценная бумага";
TinkoffSheet.Cells[_count, 4] = "Тип";
TinkoffSheet.Cells[_count, 5] = "Кол-во";
TinkoffSheet.Cells[_count, 6] = "Валюта";
TinkoffSheet.Cells[_count, 7] = "Цена покупки (средняя)";
TinkoffSheet.Cells[_count, 8] = "Стоимость покупки (средняя)";
TinkoffSheet.Cells[_count, 9] = "Дата котировки";
TinkoffSheet.Cells[_count, 10] = "Котировка в валюте";
TinkoffSheet.Cells[_count, 11] = "Изменение котировки";
TinkoffSheet.Cells[_count, 12] = "Курс к рублю";
TinkoffSheet.Cells[_count, 13] = "Котировка в рублях";
TinkoffSheet.Cells[_count, 14] = "Рыночная стоимость в валюте";
TinkoffSheet.Cells[_count, 15] = "Рыночная стоимость в рублях";
// перебираю наличные
foreach (var account in TinkoffCurrencyAccounts)
{
foreach (var pos in account.Value.currencies)
{
_count++;
TinkoffSheet.Cells[_count, 1] = account.Key;
TinkoffSheet.Cells[_count, 2] = pos.ticker;
TinkoffSheet.Cells[_count, 3] = pos.name;
TinkoffSheet.Cells[_count, 4] = pos.InstrumentTypeName;
TinkoffSheet.Cells[_count, 5] = pos.Count;
TinkoffSheet.Cells[_count, 6] = pos.currency;
TinkoffSheet.Cells[_count, 7] = pos.Price;
TinkoffSheet.Cells[_count, 8] = pos.Summa;
TinkoffSheet.Cells[_count, 9] = DateTime.Today;
TinkoffSheet.Cells[_count, 10] = pos.MarketPrice;
TinkoffSheet.Cells[_count, 11] = pos.MarketPrice - pos.Price;
TinkoffSheet.Cells[_count, 12] = pos.ValuteCurs;
TinkoffSheet.Cells[_count, 13] = pos.RubPrice;
TinkoffSheet.Cells[_count, 14] = pos.MarketSumma;
TinkoffSheet.Cells[_count, 15] = pos.RubSumma;
}
}
// перебираю ценные бумаги
foreach (var account in TinkoffPaperAccounts)
{
foreach (var pos in account.Value.positions)
{
if (pos.instrumentType == "Currency")
{
continue;
}
if (pos.averagePositionPrice == null)
{
continue;
}
_count++;
TinkoffSheet.Cells[_count, 1] = account.Key;
TinkoffSheet.Cells[_count, 2] = pos.ticker;
TinkoffSheet.Cells[_count, 3] = pos.name;
TinkoffSheet.Cells[_count, 4] = pos.InstrumentTypeName;
TinkoffSheet.Cells[_count, 5] = pos.Count;
TinkoffSheet.Cells[_count, 6] = pos.currency;
TinkoffSheet.Cells[_count, 7] = pos.Price;
TinkoffSheet.Cells[_count, 8] = pos.Summa;
TinkoffSheet.Cells[_count, 9] = DateTime.Today;
TinkoffSheet.Cells[_count, 10] = pos.MarketPrice;
TinkoffSheet.Cells[_count, 11] = pos.MarketPrice - pos.Price;
TinkoffSheet.Cells[_count, 12] = pos.ValuteCurs;
TinkoffSheet.Cells[_count, 13] = pos.RubPrice;
TinkoffSheet.Cells[_count, 14] = pos.MarketSumma;
TinkoffSheet.Cells[_count, 15] = pos.RubSumma;
}
}
// итоги
//TinkoffSheet.Cells[_footer_row, 5].FormulaR1C1 = $"=SUBTOTAL(9,R[{-stocks_rows}]C:R[-1]C)";
//TinkoffSheet.Cells[_footer_row, 14].FormulaR1C1 = $"=SUBTOTAL(9,R[{-all_rows}]C:R[-1]C)";
// оформляем
int max_columns = TinkoffSheet.UsedRange.Columns.Count;
int max_rows = TinkoffSheet.UsedRange.Rows.Count;
MSExcel.RangeBold(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[max_rows, max_columns]], false);
MSExcel.RangeBold(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[1, max_columns]], true);
MSExcel.RangeWrapText(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[1, max_columns]]);
MSExcel.RangeBorder(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[max_rows, max_columns]]);
MSExcel.RangeAutoFilter(TinkoffSheet.Cells);
MSExcel.RangeVerticalAlignment(TinkoffSheet.Cells, Excel.XlVAlign.xlVAlignCenter);
}
}
if (this.Application != null)
{
this.Application.StatusBar = false;
}
}
// Обновить все данные, связанные с котировками
private void ribbon_btnProfitClicked()
{
ribbon_btnRefreshAllClicked();
if ((this.Application != null) && (this.Application.ActiveWorkbook != null))
{
Excel.Worksheet ProfitSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelProfitName, sh => { });
Excel.Worksheet TinkoffSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelTinkoffName, sh => { });
Excel.Worksheet HistorySheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelHistoryName, sh => { });
Excel.Worksheet BrokerSheet = null;
if (HistorySheet == null)
{
// лист не найден - добавляем
HistorySheet = (Excel.Worksheet) this.Application.ActiveWorkbook.Sheets.Add();
HistorySheet.Name = ExcelHistoryName;
HistorySheet.Activate();
// заголовок
int _count = 1;
HistorySheet.Cells[_count, 1] = "Ticker";
HistorySheet.Cells[_count, 2] = "Date";
HistorySheet.Cells[_count, 3] = "IsCurrency";
HistorySheet.Cells[_count, 4] = "Count";
HistorySheet.Cells[_count, 5] = "Valute";
HistorySheet.Cells[_count, 6] = "Price";
HistorySheet.Cells[_count, 7] = "Summa";
HistorySheet.Cells[_count, 8] = "Dividend";
HistorySheet.Cells[_count, 9] = "MarketPrice";
HistorySheet.Cells[_count, 10] = "MarketSumma";
HistorySheet.Cells[_count, 11] = "Curs";
}
if (ProfitSheet != null)
{
Dictionary <string, ProfitTicker> Profits = new Dictionary <string, ProfitTicker>();
int max_rows;
int max_columns;
string ticker;
this.Application.StatusBar = $"Собираем портфели с листов данного excel-файла";
// портфель Тинькофф
if (TinkoffSheet != null)
{
max_rows = TinkoffSheet.UsedRange.Rows.Count;
for (int i = 2; i <= max_rows; i++)
{
ticker = TinkoffSheet.Cells[i, 2].Text;
if ((ticker != null) && (ticker != ""))
{
double value;
ProfitTicker item = null;
if (Profits.ContainsKey(ticker))
{
item = Profits[ticker];
}
else
{
item = new ProfitTicker();
item.ticker = ticker;
Profits.Add(ticker, item);
}
item.name = TinkoffSheet.Cells[i, 3].Text;
item.InstrumentTypeName = TinkoffSheet.Cells[i, 4].Text;
if (TinkoffSheet.Cells[i, 5].Value is double)
{
value = TinkoffSheet.Cells[i, 5].Value;
}
else
{
double.TryParse(TinkoffSheet.Cells[i, 5].Text, out value);
}
item.Count += value;
item.currency = TinkoffSheet.Cells[i, 6].Text;
if (TinkoffSheet.Cells[i, 8].Value is double)
{
value = TinkoffSheet.Cells[i, 8].Value;
}
else
{
double.TryParse(TinkoffSheet.Cells[i, 8].Text, out value);
}
item.Summa += value;
if (TinkoffSheet.Cells[i, 14].Value is double)
{
value = TinkoffSheet.Cells[i, 14].Value;
}
else
{
double.TryParse(TinkoffSheet.Cells[i, 14].Text, out value);
}
item.MarketSumma += value;
}
}
}
foreach (var broker in BrokersName)
{
// находим лист
BrokerSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, broker, sh => { });
// портфель брокера
if (BrokerSheet != null)
{
max_rows = BrokerSheet.UsedRange.Rows.Count;
for (int i = 2; i <= max_rows; i++)
{
ticker = BrokerSheet.Cells[i, 2].Text;
if ((ticker != null) && (ticker != ""))
{
double value;
ProfitTicker item = null;
if (Profits.ContainsKey(ticker))
{
item = Profits[ticker];
}
else
{
item = new ProfitTicker();
item.ticker = ticker;
Profits.Add(ticker, item);
}
item.name = BrokerSheet.Cells[i, 3].Text;
item.InstrumentTypeName = BrokerSheet.Cells[i, 4].Text;
if (BrokerSheet.Cells[i, 7].Value is double)
{
value = BrokerSheet.Cells[i, 7].Value;
}
else
{
double.TryParse(BrokerSheet.Cells[i, 7].Text, out value);
}
item.Count += value;
if (BrokerSheet.Cells[i, 8].Value is double)
{
value = BrokerSheet.Cells[i, 8].Value;
}
else
{
double.TryParse(BrokerSheet.Cells[i, 8].Text, out value);
}
item.Nominal = value;
item.currency = BrokerSheet.Cells[i, 9].Text;
if (BrokerSheet.Cells[i, 11].Value is double)
{
value = BrokerSheet.Cells[i, 11].Value;
}
else
{
double.TryParse(BrokerSheet.Cells[i, 11].Text, out value);
}
item.Summa += value;
if (BrokerSheet.Cells[i, 17].Value is double)
{
value = BrokerSheet.Cells[i, 17].Value;
}
else
{
double.TryParse(BrokerSheet.Cells[i, 17].Text, out value);
}
item.MarketSumma += value;
}
}
}
}
ProfitSheet.Activate();
max_rows = ProfitSheet.UsedRange.Rows.Count;
int count_rows = 1;
this.Application.StatusBar = $"Заполняем лист {ExcelProfitName}";
// перебираем строки в excel: если есть в портфелях - заполняем, если нет - обнуляем
for (int i = 2; i <= max_rows; i++)
{
ticker = ProfitSheet.Cells[i, 1].Text;
if ((ticker == null) || (ticker == ""))
{
break;
}
count_rows++;
if (Profits.ContainsKey(ticker))
{
Profits[ticker].IsFound = true;
FillProfit(ProfitSheet, count_rows, ticker, Profits[ticker], HistorySheet);
}
else
{
FillProfit(ProfitSheet, count_rows, ticker, null, HistorySheet);
}
this.Application.StatusBar = $"Заполняем лист {ExcelProfitName} - {ticker}";
}
// все, что не найдено - добавляем в конец таблицы
foreach (var item in Profits.Values)
{
if ((!item.IsFound) && (item.Count != 0)) //-V3024
{
ticker = item.ticker;
count_rows++;
FillProfit(ProfitSheet, count_rows, ticker, item, HistorySheet);
if (this.Application != null)
{
this.Application.StatusBar = $"Заполняем лист {ExcelProfitName} - {ticker}";
}
}
}
// итоги
count_rows++;
ProfitSheet.Cells[count_rows, 5].FormulaR1C1 = $"=SUBTOTAL(9,R[{2- count_rows}]C:R[-1]C)";
ProfitSheet.Cells[count_rows, 17].FormulaR1C1 = $"=SUBTOTAL(9,R[{2 - count_rows}]C:R[-1]C)";
max_columns = ProfitSheet.UsedRange.Columns.Count;
if (max_rows > count_rows)
{
ProfitSheet.Range[ProfitSheet.Cells[count_rows + 1, 1], ProfitSheet.Cells[max_rows, max_columns]].ClearContents();
}
max_rows = count_rows;
// оформляем
MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[max_rows, max_columns]], false);
MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[1, max_columns]], true);
MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[max_rows, 1], ProfitSheet.Cells[max_rows, max_columns]], true);
MSExcel.RangeWrapText(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[1, max_columns]]);
MSExcel.RangeBorder(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[max_rows, max_columns]]);
MSExcel.RangeAutoFilter(ProfitSheet.Cells);
MSExcel.RangeVerticalAlignment(ProfitSheet.Cells, Excel.XlVAlign.xlVAlignCenter);
// обновляем семейный бюджет
string FamilyBudgetFile = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetFile");
string FamilyBudgetInvestField = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetInvest");
string FamilyBudgetToInvestField = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetToInvest");
if ((FamilyBudgetFile != "") && (FamilyBudgetInvestField != "") && (FamilyBudgetToInvestField != "") && File.Exists(FamilyBudgetFile))
{
Excel.Workbook curWorkbook = this.Application.ActiveWorkbook;
// открываем файл семейного бюджета
Excel.Workbook familyWorkbook = this.Application.Workbooks.Open(FamilyBudgetFile);
if (familyWorkbook != null)
{
double value;
// считываем затраты на инвестиции
value = MSExcel.GetNamedRangeValue <double>(familyWorkbook, FamilyBudgetToInvestField);
MSExcel.SetNamedRangeValue <double>(curWorkbook, FamilyBudgetToInvestField, value);
if (System.Windows.Forms.MessageBox.Show("Обновить результат инвестиций в семейном бюджете ?", "Внимание!", System.Windows.Forms.MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
// заполняем результат инвестиций
value = MSExcel.GetNamedRangeValue <double>(curWorkbook, FamilyBudgetInvestField);
MSExcel.SetNamedRangeValue <double>(familyWorkbook, FamilyBudgetInvestField, value);
familyWorkbook.RefreshAll();
}
familyWorkbook.Save();
familyWorkbook.Close();
}
if (curWorkbook != null)
{
curWorkbook.Activate();
}
}
}
this.Application.ActiveWorkbook.RefreshAll();
}
if (this.Application != null)
{
this.Application.StatusBar = false;
}
}