1. 读取源数据：源数据作为参数传入处理函数（分别找到每一源数据所在行，所在列）
2. 格式处理函数：根据传入进来的参数，调用处理函数，分资产类型返回指定格式数据
3. 分资产类型函数：根据传入进来的参数，返回指定格式数据
4. 指定格式数据填充：定位目标格后，通过resize函数，将返回指定格式数据输出到指定单元格
其中格式处理函数及分资产类型函数被写成了类
【读取源数据】
Sub
Generate ()
Call
InitialData
Dim
sh As Excel.Worksheet, i As Long, tarR As Long, colDes(), col(0 To
5) As Long, asset_type As String
Set
sh = ThisWorkbook.Sheets("RM")
sh.Range("A11:Z1000").Clear
colDes = Array("Investment", "ISIN CODE", "VALUE DATE", "INT RATE",
"MATURITY DATE", "BOOK COST")
For
i = 0 To 5
col(i)
= Application.WorksheetFunction.Match(colDes(i),
_
Sheets("InvestVal_QD-CITIC-HVT").Range("7:7"), False)
Next
i
tarR
= 11
For
i = 8 To 1000
If Sheet6.Cells(i, col(1)) = ""
Then
asset_type = "cash"
Else
asset_type = "bond"
End If
If Sheet6.Cells(i, col(5)) <> "" And
Sheet6.Cells(i, col(0)) <> ""
Then
sh.Range("A"
& tarR).Resize(1, 30) = rmo.decompose(account:="QDII",
_
des:=Sheet6.Cells(i, col(0)), assettype:=asset_type, _
ticker:=Sheet6.Cells(i, col(1)), _
amount:=IIf(asset_type = "bond", Sheet6.Cells(i, col(2)),
Sheet6.Cells(i, col(5))), _
cur:=Sheet6.Cells(i, col(3)), price:=Sheet6.Cells(i,
col(4)))
tarR = tarR + 1
End If
Next
i
tarR = tarR + 1
sh.Range("A" & tarR) = "END-OF-DATA"
tarR
= tarR + 1
sh.Range("A" & tarR) = "END-OF-FILE"
'保存工作簿
Dim
wb As Excel.Workbook
Set
wb = Workbooks.Add
sh.UsedRange.Copy
wb.Sheets(1).Paste
Dim
tmp
tmp
= Application.DisplayAlerts
Application.DisplayAlerts = False
wb.SaveAs fileName:=dataPath & "RM-QDII "
& dts & ".rm3D",
FileFormat:=xlText
Application.DisplayAlerts = tmp
wb.Close False
End
Sub
【格式处理函数】
Public Function
decompose(account As String, des As String, _
Optional ticker As String, Optional amount As Double = 0, Optional
assettype As String, _
Optional cur As String = "USD", Optional price As Double =
1)
Dim industry As String, price1 As Double, country As
String
Select
Case LCase(assettype)
Case
"cash"
decompose = cash(account:=account, description:=des, _
notional:=amount, cur:=cur)
'
Case "bond future"
'
code = Left(ticker, InStr(ticker, " ") - 1) &
"|Ticker|XCBT"
'
decompose = ExchangeTraded(account:=account, des:=des, code:=code,
amount:=amount, _
'
price:=price)
'
Case "exchrate nondelivfwd"
'
' "CNYUSD NDF 07June2012"
'
Dim forwardCurrency As String, forwardDate As Date
'
forwardDate = GetDateAtLast(des)
'
forwardCurrency = Left(des, 3)
'
decompose = fxForward(account:=account, des:=des, notional:=amount,
_
'
forwardCurrency:=forwardCurrency, settleCurrency:=cur,
forwardPrice:=price, _
'
forwardDate:=forwardDate)
Case
Else ' "bond"
code = Left(ticker, 12) & "|ISIN"
industry = Application.WorksheetFunction.VLookup(des, Sheets("cb
info").Range("A:E"), 5, False)
country = Application.WorksheetFunction.VLookup(des, Sheets("cb
info").Range("A:E"), 4, False)
decompose = ExchangeTraded(account:=account, des:=des, code:=code,
amount:=amount, price:=price, tags:="|icbSector|" &
industry & "|Country|" & country,
updates:="add|marketPrice|" & price)
End If
End
Select
End Function
【分资产类型函数】
Function
cash（account As String, description As String,
_
notional As Double, cur As String）
Dim res()
ReDim res(1 To 1, 1 To 30)
res(1, 1) = "cash"
res(1, 2) = description
res(1, 5) = "clientPortfolioID|" & account
& "|icbSector|cash"
cash
= res
end Function
Function ExchangeTraded（account As String, description As String,
_
notional
As Double, cur As String）
****
End Function
Function
bond（account As String, description As String,
_
notional As Double, cur As String）
****
end Function
Function
fxForward（account As String, description As String,
_
notional As Double, cur As String）
****
End Function
加载中…
喜欢