Untitled
unknown
plain_text
8 months ago
14 kB
2
Indexable
Never
Private Sub MOF16E()
Dim lines() As String
Dim line As String
Dim i As BigInt
lines = Split(sline, "\n")
For i = LBound(lines) To UBound(lines)
If (Trim(lines(i)) <> "YSORT|TYPE|PORTFOLIO_TYPE|PORTFOLIO|GROUP_CODE|GROUP_DESCRIPTION|SECURITY_CODE|SUB_ASSET_TYPE|RESIDENCE|DESCRIPTION|CURRENCY|NOM_CUR_POSITION|NOM_VALUE_DATED|COUPON|MATURITY_DATE|HIST_CUR_PRICE|HIST_VAL_PRICE|AM_COS_CUR|AM_COS_DAT|val_cost_cur|VAL_COST_DAT|MARKET_VALUE_CUR|MARKET_VALUE_DAT|UN_PL_CUR|UN_PL_DAT|EST_INC_CUR|EST_INC_VAL|MARKET_PRICE|COUPON_PYMT_DATE|ACC_INT_CUR|ACC_INT_DAT|ISSUE_PER_CUR|ISSUE_PER_VALUE|TIER1_CUR_POS|TIER1_VALUE_DATED|DAT_LAST_TRD|LAST_TRAD|NB_PAYMENT|RATING|SUM_NOM_CUR_POS|SUM_NOM_VAL_DAT|INDUSTRY_CODE|TOTAL_ISSUED") Then
Tabit = Split(lines(i), "|")
If UBound(Tabit) > 0 Then
Sql_Rst.AddNew
CC = CC + 1
Sql_Rst![MOF16E_batchdate] = Format(Logsdate, "yyyy/mm/dd")
Sql_Rst![MOF16E_counter] = CC
If Trim(Tabit(0)) <> "" Then
Sql_Rst![MOF16E_sort] = Get_number(Trim(Tabit(0)))
Else
Sql_Rst![MOF16E_sort] = "1"
End If
Sql_Rst![MOF16E_type] = Trim(Tabit(1))
Sql_Rst![MOF16E_portfolio_type] = Trim(Tabit(2))
Sql_Rst![MOF16E_portfolio] = Trim(Tabit(3))
Sql_Rst![MOF16E_group_code] = Trim(Tabit(4))
Sql_Rst![MOF16E_group_description] = Trim(Tabit(5))
Sql_Rst![MOF16E_security_code] = Trim(Tabit(6))
Sql_Rst![MOF16E_sub_asset_Type] = Trim(Tabit(7))
Sql_Rst![MOF16E_residence] = Trim(Tabit(8))
Sql_Rst![MOF16E_description] = Trim(Tabit(9))
Sql_Rst![MOF16E_CURRENCY] = Trim(Tabit(10))
Sql_Rst![MOF16E_NOM_CUR_POSITION] = Get_number(Trim(Tabit(11)))
Sql_Rst![MOF16E_NOM_VALUE_DATED] = Get_number(Trim(Tabit(12)))
Sql_Rst![MOF16E_coupon] = Get_number(Trim(Tabit(13)))
If Trim(Tabit(14)) <> "" Then
Sql_Rst![MOF16E_MATURITY_DATE] = DateSerial(Mid(Trim(Tabit(14)), 1, 4), Mid(Trim(Tabit(14)), 6, 2), Mid(Trim(Tabit(14)), 9, 2))
Else
Sql_Rst![MOF16E_MATURITY_DATE] = "1900/01/01"
End If
Sql_Rst![MOF16E_hist_cur_price] = Get_number(Trim(Tabit(15)))
Sql_Rst![MOF16E_hist_val_price] = Get_number(Trim(Tabit(16)))
Sql_Rst![MOF16E_am_cos_cur] = Get_number(Trim(Tabit(17)))
Sql_Rst![MOF16E_am_cos_dat] = Get_number(Trim(Tabit(18)))
Sql_Rst![MOF16E_val_cost_cur] = Get_number(Trim(Tabit(19)))
Sql_Rst![MOF16E_val_cost_dat] = Get_number(Trim(Tabit(20)))
Sql_Rst![MOF16E_market_price] = Get_number(Trim(Tabit(27)))
Sql_Rst![MOF16E_market_value_cur] = Get_number(Trim(Tabit(21)))
Sql_Rst![MOF16E_market_value_dat] = Get_number(Trim(Tabit(22)))
Sql_Rst![MOF16E_unpl_cur] = Get_number(Trim(Tabit(23)))
Sql_Rst![MOF16E_unpl_dat] = Get_number(Trim(Tabit(24)))
If Trim(Tabit(28)) <> "" Then
Sql_Rst![MOF16E_COUPON_PYMT_DATE] = DateSerial(Mid(Trim(Tabit(28)), 1, 4), Mid(Trim(Tabit(28)), 6, 2), Mid(Trim(Tabit(28)), 9, 2))
Else
Sql_Rst![MOF16E_COUPON_PYMT_DATE] = "1900/01/01"
End If
Sql_Rst![MOF16E_EST_CUR] = Get_number(Trim(Tabit(25)))
Sql_Rst![MOF16E_EST_DAT] = Get_number(Trim(Tabit(26)))
Sql_Rst![MOF16E_acc_cur] = Get_number(Trim(Tabit(29)))
Sql_Rst![MOF16E_acc_dat] = Get_number(Trim(Tabit(30)))
Sql_Rst![MOF16E_issue_cur] = Get_number(Trim(Tabit(31)))
Sql_Rst![MOF16E_issue_dat] = Get_number(Trim(Tabit(32)))
Sql_Rst![MOF16E_tier_cur] = Get_number(Trim(Tabit(33)))
Sql_Rst![MOF16E_tier_dat] = Get_number(Trim(Tabit(34)))
If Trim(Tabit(35)) <> "" Then
Sql_Rst![MOF16E_dat_last_trd] = DateSerial(Mid(Trim(Tabit(35)), 1, 4), Mid(Trim(Tabit(35)), 6, 2), Mid(Trim(Tabit(35)), 9, 2))
Else
Sql_Rst![MOF16E_dat_last_trd] = "1900/01/01"
End If
Sql_Rst![MOF16E_last_trad] = Get_number(Trim(Tabit(36)))
Sql_Rst![MOF16E_NB_PAYMENT] = Get_number(Trim(Tabit(37)))
Sql_Rst![MOF16E_RATING] = Trim(Tabit(38))
Sql_Rst![MOF16E_SUM_POS] = Get_number(Trim(Tabit(39)))
Sql_Rst![MOF16E_SUM_VAL] = Get_number(Trim(Tabit(40)))
Sql_Rst![MOF16E_INDUSTRY_CODE] = Trim(Tabit(41))
Select Case Sql_Rst![MOF16E_INDUSTRY_CODE]
Case 10, 11, 20, 30, 40, 130, 140, 160
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 1
Case 70, 75, 80, 90, 91, 100, 110
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 4
Case 50, 51, 52, 53, 54, 55, 60, 61, 62, 170, 171, 172, 297
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 2
Case 92, 93, 105, 120, 125, 131, 150, 180, 190, 200, 201, 202, 203, 204, 205, 210, 220, 230, 240, 250, 260, 270, 275, 280, 285, 290, 291, 292, 293, 294, 295, 296, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 310, 312, 314, 316, 320, 330, 332, 340, 350, 360, 362, 370, 380, 390, 400, 401, 410, 420, 430, 440, 442, 450, 460, 470, 480, 490, 496, 500, 501, 502, 503, 504, 505, 506, 510, 520, 530, 540, 550, 560, 570, 576, 578, 580, 590, 596, 600, 610, 620, 630, 640, 650, 660
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 3
End Select
Select Case Sql_Rst![MOF16E_sub_asset_Type]
Case 100, 110, 120, 130, 140, 150, 160, 170, 180, 182, 190, 200, 210, 232, 233, 235
Sql_Rst![MOF16E_GROUP_SAT] = 2
Case 183, 234
Sql_Rst![MOF16E_GROUP_SAT] = 4
Case 191, 192, 220, 230, 231
Sql_Rst![MOF16E_GROUP_SAT] = 5
Case 238, 240, 250, 260
Sql_Rst![MOF16E_GROUP_SAT] = 1
Case 270, 272, 273, 280, 290, 300, 310, 320, 330, 340
Sql_Rst![MOF16E_GROUP_SAT] = 6
Case 101, 102
Sql_Rst![MOF16E_GROUP_SAT] = 3
End Select
Sql_Rst![MOF16E_TOTALISSUE] = Get_number(Trim(Tabit(42)))
Sql_Rst.Update
Else
If Trim(Mid(sline, 7, 40)) <> "" Then
Sql_Rst.AddNew
CC = CC + 1
Sql_Rst![MOF16E_batchdate] = Format(Logsdate, "yyyy/mm/dd")
Sql_Rst![MOF16E_counter] = CC
If Trim(Mid(sline, 1, 5)) <> "" Then
Sql_Rst![MOF16E_sort] = Get_number(Trim(Mid(sline, 1, 5)))
Else
Sql_Rst![MOF16E_sort] = "1"
End If
Sql_Rst![MOF16E_type] = Trim(Mid(sline, 7, 40))
Sql_Rst![MOF16E_portfolio_type] = Trim((Mid(sline, 49, 40)))
Sql_Rst![MOF16E_portfolio] = Trim(Mid(sline, 91, 10))
Sql_Rst![MOF16E_group_code] = Trim(Mid(sline, 103, 5))
Sql_Rst![MOF16E_group_description] = Trim(Mid(sline, 110, 40))
Sql_Rst![MOF16E_security_code] = Trim(Mid(sline, 152, 20))
Sql_Rst![MOF16E_sub_asset_Type] = Trim(Mid(sline, 175, 3))
Sql_Rst![MOF16E_residence] = Trim(Mid(sline, 180, 3))
Sql_Rst![MOF16E_description] = Trim(Mid(sline, 185, 50))
Sql_Rst![MOF16E_CURRENCY] = Trim(Mid(sline, 237, 3))
Sql_Rst![MOF16E_NOM_CUR_POSITION] = Get_number(Trim(Mid(sline, 242, 19)))
Sql_Rst![MOF16E_NOM_VALUE_DATED] = Get_number(Trim(Mid(sline, 263, 19)))
Sql_Rst![MOF16E_coupon] = Get_number(Trim(Mid(sline, 285, 19)))
If Trim(Mid(sline, 306, 4)) <> "" Then
Sql_Rst![MOF16E_MATURITY_DATE] = DateSerial(Trim(Mid(sline, 306, 4)), Trim(Mid(sline, 310, 2)), Trim(Mid(sline, 312, 2)))
Else
Sql_Rst![MOF16E_MATURITY_DATE] = "1900/01/01"
End If
Sql_Rst![MOF16E_hist_cur_price] = Get_number(Trim((Mid(sline, 319, 20))))
Sql_Rst![MOF16E_hist_val_price] = Get_number(Trim(Mid(sline, 341, 20)))
Sql_Rst![MOF16E_am_cos_cur] = Get_number(Trim(Mid(sline, 363, 20)))
Sql_Rst![MOF16E_am_cos_dat] = Get_number(Trim(Mid(sline, 385, 20)))
Sql_Rst![MOF16E_val_cost_cur] = Get_number(Trim(Mid(sline, 407, 20)))
Sql_Rst![MOF16E_val_cost_dat] = Get_number(Trim(Mid(sline, 429, 20)))
Sql_Rst![MOF16E_market_price] = Get_number(Trim(Mid(sline, 451, 20)))
Sql_Rst![MOF16E_market_value_cur] = Get_number(Trim(Mid(sline, 473, 20)))
Sql_Rst![MOF16E_market_value_dat] = Get_number(Trim(Mid(sline, 495, 20)))
Sql_Rst![MOF16E_unpl_cur] = Get_number(Trim(Mid(sline, 517, 20)))
Sql_Rst![MOF16E_unpl_dat] = Get_number(Trim(Mid(sline, 539, 20)))
If Trim(Mid(sline, 562, 4)) <> "" Then
Sql_Rst![MOF16E_COUPON_PYMT_DATE] = DateSerial(Trim(Mid(sline, 562, 4)), Trim(Mid(sline, 566, 2)), Trim(Mid(sline, 568, 2)))
Else
Sql_Rst![MOF16E_COUPON_PYMT_DATE] = "1900/01/01"
End If
Sql_Rst![MOF16E_EST_CUR] = Get_number(Trim(Mid(sline, 575, 20)))
Sql_Rst![MOF16E_EST_DAT] = Get_number(Trim((Mid(sline, 597, 20))))
Sql_Rst![MOF16E_acc_cur] = Get_number(Trim(Mid(sline, 619, 20)))
Sql_Rst![MOF16E_acc_dat] = Get_number(Trim(Mid(sline, 641, 20)))
Sql_Rst![MOF16E_issue_cur] = Get_number(Trim(Mid(sline, 663, 20)))
Sql_Rst![MOF16E_issue_dat] = Get_number(Trim(Mid(sline, 685, 20)))
Sql_Rst![MOF16E_tier_cur] = Get_number(Trim(Mid(sline, 707, 20)))
Sql_Rst![MOF16E_tier_dat] = Get_number(Trim(Mid(sline, 729, 20)))
If Trim(Mid(sline, 751, 4)) <> "" Then
Sql_Rst![MOF16E_dat_last_trd] = DateSerial(Trim(Mid(sline, 751, 4)), Trim(Mid(sline, 755, 2)), Trim(Mid(sline, 757, 2)))
Else
Sql_Rst![MOF16E_dat_last_trd] = "1900/01/01"
End If
Sql_Rst![MOF16E_last_trad] = Get_number(Trim(Mid(sline, 764, 20)))
Sql_Rst![MOF16E_NB_PAYMENT] = Get_number(Trim(Mid(sline, 786, 3)))
Sql_Rst![MOF16E_RATING] = Trim(Mid(sline, 810, 35))
Sql_Rst![MOF16E_SUM_POS] = Get_number(Trim(Mid(sline, 852, 19)))
Sql_Rst![MOF16E_SUM_VAL] = Get_number(Trim(Mid(sline, 874, 19)))
Sql_Rst![MOF16E_INDUSTRY_CODE] = Trim(Mid(sline, 895, 5))
Select Case Sql_Rst![MOF16E_INDUSTRY_CODE]
Case 10, 11, 20, 30, 40, 130, 140, 160
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 1
Case 70, 75, 80, 90, 91, 100, 110
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 4
Case 50, 51, 52, 53, 54, 55, 60, 61, 62, 170, 171, 172, 297
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 2
Case 92, 93, 105, 120, 125, 131, 150, 180, 190, 200, 201, 202, 203, 204, 205, 210, 220, 230, 240, 250, 260, 270, 275, 280, 285, 290, 291, 292, 293, 294, 295, 296, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 310, 312, 314, 316, 320, 330, 332, 340, 350, 360, 362, 370, 380, 390, 400, 401, 410, 420, 430, 440, 442, 450, 460, 470, 480, 490, 496, 500, 501, 502, 503, 504, 505, 506, 510, 520, 530, 540, 550, 560, 570, 576, 578, 580, 590, 596, 600, 610, 620, 630, 640, 650, 660
Sql_Rst![MOF16E_GROUP_INDUSTRY] = 3
End Select
Select Case Sql_Rst![MOF16E_sub_asset_Type]
Case 100, 110, 120, 130, 140, 150, 160, 170, 180, 182, 190, 200, 210, 232, 233, 235
Sql_Rst![MOF16E_GROUP_SAT] = 2
Case 183, 234
Sql_Rst![MOF16E_GROUP_SAT] = 4
Case 191, 192, 220, 230, 231
Sql_Rst![MOF16E_GROUP_SAT] = 5
Case 238, 240, 250, 260
Sql_Rst![MOF16E_GROUP_SAT] = 1
Case 270, 272, 273, 280, 290, 300, 310, 320, 330, 340
Sql_Rst![MOF16E_GROUP_SAT] = 6
Case 101, 102
Sql_Rst![MOF16E_GROUP_SAT] = 3
End Select
Sql_Rst![MOF16E_TOTALISSUE] = Get_number(Trim(Mid(sline, 905, 19)))
Sql_Rst.Update
End If
End If
End If
Next i
End SubLeave a Comment