private void Rollover() { long j; int i; long k; string ls_Date; string ls_RollDate; Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); DataTable dt_io; DataTable dt_cf;
InitSetupForRollover();
dt_io = dict["Initial Balance"];
dt_cf = dict["CF Balance"];
dt_io.Rows["2:60000"].ClearContents();
k = dt_cf.Rows.Count;
if (k > 1)
{
UpdateStatus("Retrieving Brought Forward Data from last carried forward data");
ls_Date = dt_cf.Rows[2][2].ToString();
dict["Report Date"].Rows[2][6] = Format(CDate(ls_Date), "dd-mmm");
ls_RollDate = Format(DateAdd("d", 1, CDate(ls_Date)), "dd-mmm-yyyy");
for (j = 2; j <= k; j++)
{
for (i = 3; i <= 21; i++)
{
dt_io.Rows[j][0] = dt_cf.Rows[j][0];
dt_io.Rows[j][1] = ls_RollDate;
dt_io.Rows[j][i] = dt_cf.Rows[j][i];
}
dt_io.Rows[j][26] = Right("000" + dt_cf.Rows[j][24].ToString(), 2); //investment type
dt_io.Columns[26].DataType = typeof(string);
}
}
else
{
GetInitialDataFromHldg();
}
dict["Report Date"].Columns[1].ClearContents();
dict["Report Fund"].Columns[1].ClearContents();
}
private void InitSetupForRollover() { Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); dict["Menu"].Cells[19, 4].Value = ""; InitMonth(); GetActions(); GetSigns(); GetBrokers(); InitClassification(); InitKeyType(); }
public bool InitMonth() { DateTime ld_Date; int i; string[] gs_Mth = new string[12]; for (i = 1; i <= 12; i++) { ld_Date = DateTime.ParseExact("27/" + i + "/" + DateTime.Now.Year, "dd/MM/yyyy", CultureInfo.InvariantCulture); gs_Mth[i - 1] = ld_Date.ToString("MMM"); } gcs_YearEnd = Sheets["Report Date"].Cells[2, 6].Value; return true; }
private void GetActions() { int j = 2; string ls_Txn; string ls_Action; go_Action = null; go_Action = new Collection(); Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); DataTable dt_io = dict["Action"]; while (dt_io.Rows[j][0].ToString() != "") { ls_Txn = dt_io.Rows[j][0].ToString(); ls_Action = dt_io.Rows[j][1].ToString(); go_Action.Add(ls_Action, ls_Txn); j = j + 1; } }
private void GetSigns() { int j = 2; string ls_Txn; int li_Sign; go_Sign = null; go_Sign = new Collection(); Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); DataTable dt_io = dict["Action"]; while (dt_io.Rows[j][0].ToString() != "") { ls_Txn = dt_io.Rows[j][0].ToString(); li_Sign = Convert.ToInt32(dt_io.Rows[j][2]); go_Sign.Add(li_Sign, ls_Txn); j = j + 1; } }
private void GetBrokers() { int j = 2; string ls_BrokerID; string ls_Action; go_Broker = null; go_Broker = new Collection(); Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); DataTable dt_io = dict["ActionBroker"]; while (dt_io.Rows[j][0].ToString() != "") { ls_BrokerID = dt_io.Rows[j][1].ToString(); ls_Action = dt_io.Rows[j][0].ToString(); try { go_Broker.Add(ls_Action, ls_BrokerID); } catch (Exception ex) { // Handle exception } j = j + 1; } }
private void InitClassification() { int j = 2; string ls_InvType; string ls_class; go_Classification = null; go_Classification = new Collection(); Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); DataTable dt_io = dict["Classification"]; while (dt_io.Rows[j][0].ToString() != "") { ls_InvType = dt_io.Rows[j][0].ToString(); ls_class = dt_io.Rows[j][1].ToString(); go_Classification.Add(ls_class.ToUpper(), ls_InvType); j = j + 1; } }
private void InitKeyType() { int j = 2; string ls_InvType; string ls_KeyType; go_KeyType = null; go_KeyType = new Collection(); Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); DataTable dt_io = dict["Classification"]; while (dt_io.Rows[j][0].ToString() != "") { ls_InvType = dt_io.Rows[j][0].ToString(); ls_KeyType = dt_io.Rows[j][2].ToString(); go_KeyType.Add(ls_KeyType, ls_InvType); j = j + 1; } }
private void GetInitialDataFromHldg() { string ls_Fund; string ls_Date; int li_Fund; int li_Date; string ls_AssetID; string ls_Name; DataTable dt_io; DataTable dt_mo; long i; long j; long k; long li_StartRow; long li_LastRow; string ls_Txn; string ls_Over; int li_FinYear; string ls_class; long li_Multiplier; string ls_NoOfShare; string ls_AssetClass; string ls_IFRSBaseMktVal; string ls_InvType; int li_LastMonthIndex; string ls_LastMonth; DateTime ld_LastMonthDate; Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>(); dt_io = dict["Initial Balance"];
try
{
k = 1;
li_LastRow = 1;
while (dict["Report Fund"].Cells[k, 1].Value != "")
{
li_StartRow = 0;
ls_Fund = dict["Report Fund"].Cells[k, 1].Value;
li_FinYear = dict["Report Date"].Cells[2, 5].Value;
li_LastMonthIndex = dict["Report Date"].Cells[1, 2].Value;
ls_LastMonth = dict["Report Date"].Cells[li_LastMonthIndex, 1].Value;
ld_LastMonthDate = DateTime.Parse(ls_LastMonth);
ls_Name = "Holding" + " " + ld_LastMonthDate.ToString("MMMyyyy");
dt_mo = dict[ls_Name];
li_StartRow = dt_mo.Columns[GetHCol("Fund")].Find(ls_Fund, LookIn: XlFindLookIn.xlValues).Row;
if (li_StartRow != 0)
{
j = li_LastRow;
i = li_StartRow;
string ls_tmpFund = GetHVal(i, "Fund");
while (ls_tmpFund == ls_Fund)
{
j++;
dt_io.Rows[j][1].Value = ls_tmpFund;
ls_AssetID = GetHVal(i, "CUSIP Number");
dt_io.Rows[j][2].Value = DateTime.Parse(GetHVal(i, "Period End Date")).AddDays(1).ToString("dd-MMM-yyyy");
dt_io.Rows[j][4].Value = GetHVal(i, "Trade Country Code");
dt_io.Rows[j][5].Value = GetHVal(i, "Local Currency Code");
dt_io.Rows[j][6].Value = GetHVal(i, "ISIN Number");
dt_io.Rows[j][7].Value = GetHVal(i, "SEDOL Number");
dt_io.Rows[j][8].Value = ls_AssetID;
dt_io.Rows[j][9].Value = GetHVal(i, "Security Name");
dt_io.Rows[j][10].Value = GetHVal(i, "Security Description");
dt_io.Rows[j][11].Value = GetHVal(i, "Local Unit Cost");
ls_NoOfShare = GetHVal(i, "Shares/Par Value");
dt_io.Rows[j][17].Value = GetHVal(i, "Local Price Amount");
ls_AssetClass = GetHVal(i, "Asset Class").ToUpper();
ls_InvType = ("0000" + GetHVal(i, "Investment Type Code")).Substring(GetHVal(i, "Investment Type Code").Length - 2);
ls_class = go_Classification(ls_InvType);
if (ls_class == GCS_BOND.ToUpper() && ls_AssetClass != "")
{
ls_class = ls_AssetClass;
}
dt_io.Rows[j][18].Value = ls_class;
dt_io.Rows[j][4].Value = go_TC(ls_AssetID);
dt_io.Rows[j][6].Value = go_ISIN(ls_AssetID);
dt_io.Rows[j][18].Value = go_OClass(ls_AssetID);
dt_io.Rows[j][19].Value = GetHVal(i, "Interest Rate");
dt_io.Rows[j][20].Value = toDate(GetHVal(i, "Maturity Date"));
dt_io.Rows[j][21].Value = GetHVal(i, "Foreign Exchange Rate");
li_Multiplier = GetHVal(i, "Multiplier");
dt_io.Rows[j][23].Value = li_Multiplier;
switch (ls_class)
{
case "Futures":
dt_io.Rows[j][12].Value = GetHVal(i, "Local Notional Cost");
dt_io.Rows[j][13].Value = GetHVal(i, "Base Notional Cost");
dt_io.Rows[j][14].Value = GetHVal(i, "Local Current Notional Value");
dt_io.Rows[j][15].Value = GetHVal(i, "Base Current Notional Value");
dt_io.Rows[j][16].Value = CStr(CDec(ls_NoOfShare) / li_Multiplier);
if (CDec(ls_NoOfShare) >= 0)
{
dt_io.Rows[j][24].Value = 1;
}
else
{
dt_io.Rows[j][24].Value = 2;
}
break;
default:
dt_io.Rows[j][16].Value = ls_NoOfShare;
dt_io.Rows[j][12].Value = GetHVal(i, "Local Total Cost Amount");
dt_io.Rows[j][13].Value = GetHVal(i, "Base Total Cost");
dt_io.Rows[j][14].Value = GetHVal(i, "Local Total Cost Amount");
dt_io.Rows[j][15].Value = GetHVal(i, "Base Total Cost");
break;
}
dt_io.Rows[j][26].Value = ls_InvType;
dt_io.Rows[j][27].Value = dt_mo.Rows[i][100].Value;
dt_io.Rows[j][28].Value = dt_mo.Rows[i][101].Value;
dt_io.Rows[j][29].Value = dt_mo.Rows[i][102].Value;
dt_io.Rows[j][30].Value = dt_mo.Rows[i][103].Value;
i++;
ls_tmpFund = GetHVal(i, "Fund");
}
li_LastRow = j;
}
k++;
}
dict["Report Date"].Cells[2, 5].Value = li_FinYear + 1;
}
catch (Exception ex)
{
dict["Menu"].Cells[GCS_STATUS_ROW, GCS_STATUS_COL].Value = "[GetInitialDataFromHldg]:" + ex.Message;
}
}
private string GetHVal(long pi_Row, string ps_Field) { string result = ""; try { result = dt_mo.Rows[(int)pi_Row][GetHCol(ps_Field)].ToString(); } catch (Exception ex) { result = ""; } return result; }
private int GetHCol(string ps_Field) { int result = 0; try { result = dt_mo.Columns[ps_Field].Ordinal; } catch (Exception ex) { result = 0; } return result; }
Private Sub ImportH() Dim ls_file As String Dim ls_val As String Dim i As Integer 'Dim j As Long Dim k As Long Dim li_Period As Integer Dim li_Fund As Integer Dim ls_Date As String Dim ls_Fund As String Dim ls_tmpFund As String Dim dt_PreHolding As DataTable = dict("PreHolding") Dim dt_Holding As DataTable = dict("Holding") Dim dt_ReportDate As DataTable = dict(gcs_ReportDateShtName) Dim dt_ReportFund As DataTable = dict(gcs_ReportFundShtName) Dim ls_Name As String Dim li_Manager As Integer Dim ls_Manager As String Try dt_ReportDate.Rows(GCS_STATUS_ROW)(GCS_STATUS_COL) = "" HideSheets()
ls_file = dt_ReportDate.Rows(4)(3).ToString()
dt_PreHolding.Clear()
ReadFromXLSToDT(ls_file, dt_PreHolding, 150)
dt_Holding = dt_PreHolding.Copy()
li_Fund = GetHCol("Fund")
li_Period = GetHCol("Period End Date")
li_Manager = GetHCol("Manager Name")
ls_Date = dt_Holding.Rows(1)(li_Period).ToString()
For i = 1 To 12
If dt_ReportDate.Rows(i)(1).ToString() = "" Or _
dt_ReportDate.Rows(i)(1).ToString() = ls_Date Then
dt_ReportDate.Rows(i)(1) = ls_Date
Exit For
End If
Next
ls_Fund = ""
For k = 2 To dt_Holding.Rows.Count
ls_tmpFund = dt_Holding.Rows(k)(li_Fund).ToString()
ls_Manager = dt_Holding.Rows(k)(li_Manager).ToString()
If ls_Fund <> ls_tmpFund Then
ls_Fund = ls_tmpFund
For i = 1 To 30
If dt_ReportFund.Rows(i)(1).ToString() = "" Or _
dt_ReportFund.Rows(i)(1).ToString() = ls_Fund Then
dt_ReportFund.Rows(i)(1) = ls_Fund
dt_ReportFund.Rows(i)(3) = ls_Manager
Exit For
End If
Next
End If
Next
ls_Name = "Holding" & " " & DateTime.Parse(ls_Date).ToString("MMMyyyy")
If Not dict.ContainsKey(ls_Name) Then
dict.Add(ls_Name, dt_Holding.Copy())
Else
dict(ls_Name) = dt_Holding.Copy()
End If
dict(ls_Name).Columns.Add("IFRS Base Market Value")
dict(ls_Name).Columns.Add("IFRS Base Price")
dict(ls_Name).Columns.Add("IFRS Local Market Value")
dict(ls_Name).Columns.Add("IFRS Local Price")
dt_ReportDate.Rows(GCS_STATUS_ROW)(GCS_STATUS_COL) = ""
Application.ScreenUpdating = True
Catch ex As Exception
dt_ReportDate.Rows(GCS_STATUS_ROW)(GCS_STATUS_COL) = ex.Message
Application.ScreenUpdating = True
End Try
End Sub
private void ImportT() { string ls_file; string ls_val; int i; int k; int li_Period; int li_Fund; string ls_Date; string ls_Fund; string ls_tmpFund; DataTable dt_PreTrade = dict["PreTrade"]; DataTable dt_Trade = dict["Trade"]; DataTable dt_ReportDate = dict[gcs_ReportDateShtName]; DataTable dt_ReportFund = dict[gcs_ReportFundShtName]; string ls_Name; int li_Manager; string ls_Manager; try { dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ""; HideSheets(); ls_file = dt_ReportDate.Rows[4][3].ToString(); dt_PreTrade.Clear(); ReadFromXLSToDT(ls_file, dt_PreTrade, 150); dt_Trade = dt_PreTrade.Copy(); li_Fund = GetTCol("Fund"); li_Period = GetTCol("Period End Date"); li_Manager = GetTCol("Manager Name"); ls_Date = dt_Trade.Rows[1][li_Period].ToString(); for (i = 1; i <= 12; i++) { if (dt_ReportDate.Rows[i][1].ToString() == "" || dt_ReportDate.Rows[i][1].ToString() == ls_Date) { dt_ReportDate.Rows[i][1] = ls_Date; break; } } ls_Fund = ""; for (k = 2; k <= dt_Trade.Rows.Count; k++) { ls_tmpFund = dt_Trade.Rows[k][li_Fund].ToString(); ls_Manager = dt_Trade.Rows[k][li_Manager].ToString(); if (ls_Fund != ls_tmpFund) { ls_Fund = ls_tmpFund; for (i = 1; i <= 30; i++) { if (dt_ReportFund.Rows[i][1].ToString() == "" || dt_ReportFund.Rows[i][1].ToString() == ls_Fund) { dt_ReportFund.Rows[i][1] = ls_Fund; dt_ReportFund.Rows[i][3] = ls_Manager; break; } } } } ls_Name = "Trade" + " " + DateTime.Parse(ls_Date).ToString("MMMyyyy"); if (!dict.ContainsKey(ls_Name)) { dict.Add(ls_Name, dt_Trade.Copy()); } else { dict[ls_Name] = dt_Trade.Copy(); } dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ""; Application.ScreenUpdating = true; } catch (Exception ex) { dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ex.Message; Application.ScreenUpdating = true; } }
private void ImportA() { string ls_file; string ls_val; int i; int j = 0; int k; int li_Period; int li_Fund; string ls_Date; string ls_Fund; string ls_tmpFund; DataTable dt_CorpAction = dict["CorpAction"]; DataTable dt_ReportDate = dict[gcs_ReportDateShtName]; DataTable dt_ReportFund = dict[gcs_ReportFundShtName]; string ls_Name; try { dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ""; HideSheets(); dt_CorpAction.Clear(); ls_file = dt_ReportDate.Rows[6][3].ToString(); dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = "Records 0 Loaded"; using (var reader = new StreamReader(ls_file)) { while (!reader.EndOfStream) { var line = reader.ReadLine(); var values = line.Split(','); for (i = 1; i <= 23; i++) { ls_val = values[i - 1]; dt_CorpAction.Rows.Add(); dt_CorpAction.Rows[j][i] = ls_val; } j++; dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = "Records " + j + " Loaded"; } } li_Fund = GetTCol("Fund"); li_Period = GetTCol("Period End Date"); ls_Date = DateTime.Parse(dt_CorpAction.Rows[1][li_Period].ToString()).ToString("MM/dd/yyyy"); for (i = 1; i <= 12; i++) { if (dt_ReportDate.Rows[i][1].ToString() == "" || DateTime.Parse(dt_ReportDate.Rows[i][1].ToString()).ToString("MM/dd/yyyy") == ls_Date) { dt_ReportDate.Rows[i][1] = ls_Date; break; } } ls_Fund = ""; for (k = 2; k <= j; k++) { ls_tmpFund = dt_CorpAction.Rows[k][li_Fund].ToString(); if (ls_Fund != ls_tmpFund) { ls_Fund = ls_tmpFund; for (i = 1; i <= 30; i++) { if (dt_ReportFund.Rows[i][1].ToString() == "" || dt_ReportFund.Rows[i][1].ToString() == ls_Fund) { dt_ReportFund.Rows[i][1] = ls_Fund; break; } } } } DataTable dt_Copy = dt_CorpAction.Copy(); dt_Copy.TableName = gcs_CorpActionShtName + " " + DateTime.Parse(ls_Date).ToString("MMMyyyy"); if (!dict.ContainsKey(dt_Copy.TableName)) { dict.Add(dt_Copy.TableName, dt_Copy); } else { dict[dt_Copy.TableName] = dt_Copy; } DataRow headerRow = dt_Copy.NewRow(); for (i = 1; i <= 23; i++) { headerRow[i] = dt_CorpAction.Rows[1][i]; } dt_Copy.Rows.InsertAt(headerRow, 0); dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ""; Application.ScreenUpdating = true; } catch (Exception ex) { dt_ReportDate.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ex.Message; Application.ScreenUpdating = true; } }
private void ImportCusip() { string ls_file; string ls_val; int i; long j = 0; long k; int li_Period; int li_Fund; string ls_Date; string ls_Fund; string ls_tmpFund; DataTable dt_Cusip = dict[gcs_CusipShtName]; DataTable dt_Menu = dict[gcs_MenuShtName]; DataTable dt_ReportDate = dict[gcs_ReportDateShtName]; string ls_Name; try { dt_Menu.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ""; dt_Cusip.Clear(); ls_file = dt_Menu.Rows[9][3].ToString(); using (var reader = new StreamReader(ls_file)) { while (!reader.EndOfStream) { var line = reader.ReadLine(); var values = line.Split(','); for (i = 1; i <= 8; i++) { ls_val = values[i - 1]; dt_Cusip.Rows.Add(); dt_Cusip.Rows[(int)j][i] = ls_val; } j++; dt_Menu.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = "Records " + (j - 1) + " Loaded"; } } dt_Cusip.Columns[1].DataType = typeof(DateTime); dt_Cusip.Columns[2].DataType = typeof(DateTime); dt_Cusip.Columns[7].DataType = typeof(DateTime); dt_Cusip.Columns[1].DateTimeMode = DataSetDateTime.Unspecified; dt_Cusip.Columns[2].DateTimeMode = DataSetDateTime.Unspecified; dt_Cusip.Columns[7].DateTimeMode = DataSetDateTime.Unspecified; dt_Cusip.Columns[3].MaxLength = 255; dt_Cusip.Columns[4].MaxLength = 255; dt_Cusip.Columns[5].MaxLength = 255; dt_Cusip.Columns[8].MaxLength = 255; ls_Date = DateTime.Parse(dt_Cusip.Rows[1][1].ToString()).ToString("MM/dd/yyyy"); DataTable dt_Copy = dt_Cusip.Copy(); dt_Copy.TableName = gcs_CusipShtName + " " + DateTime.Parse(ls_Date).ToString("MMMyyyy"); if (!dict.ContainsKey(dt_Copy.TableName)) { dict.Add(dt_Copy.TableName, dt_Copy); } else { dict[dt_Copy.TableName] = dt_Copy; } dt_Menu.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ""; } catch (Exception ex) { dt_Menu.Rows[GCS_STATUS_ROW][GCS_STATUS_COL] = ex.Message; } }
