example FinanceColim1
####################################
# Financial Data Warehousing
typeside Ty = literal {
external_types
Integer -> "java.lang.Integer"
String -> "java.lang.String"
Double -> "java.lang.Double"
Boolean -> "java.lang.String"
Date -> "java.lang.String"
external_parsers
Integer -> "parseInt;"
String -> "x => x"
Double -> "x => java.lang.Double.parseDouble(x)"
Boolean -> "x => x"
Date -> "x => x"
external_functions
keygen : Integer,Integer,Integer -> Integer = "(x, y, z) => (2^x * 3^y * 5^z)"
}
####################################
schema Client = literal : Ty {
entities
client
attributes
id : client -> Integer
name description : client -> String
}
instance ClientInstance = literal : Client {
generators
cc1 cc2 cc3 : client
multi_equations
id -> {cc1 1, cc2 2, cc3 3}
name -> {cc1 Tom, cc2 Dick, cc3 Harry}
description -> {cc1 "Tom Client", cc2 "Dick Client", cc3 "Harry Client"}
}
####################################
schema Portfolio = literal : Ty {
entities
strategy
portfolio
foreign_keys
strategy : portfolio -> strategy
parent : portfolio -> portfolio
path_equations
portfolio.parent.parent = portfolio.parent
attributes
id : strategy -> Integer
name description : strategy -> String
id : portfolio -> Integer
name description : portfolio -> String
client_id strategy_id parent_id : portfolio -> Integer
observation_equations
forall p. p.strategy.id = p.strategy_id
forall p. p.parent.id = p.parent_id
}
instance PortfolioInstance = literal : Portfolio {
generators
ps1 ps2 ps3 : strategy
pp1 pp2 pp3 : portfolio
multi_equations
parent_id -> {pp1 1, pp2 2, pp3 2}
parent -> {pp1 pp1, pp2 pp2, pp3 pp2}
id -> {ps1 1, ps2 2, ps3 3, pp1 1, pp2 2, pp3 3}
name -> {ps1 Strat1, ps2 Strat2, ps3 Strat3, pp1 Port1, pp2 Port2, pp3 Port3}
description -> {ps1 Strategy1, ps2 Strategy2, ps3 Strategy3, pp1 Portfolio1, pp2 Portfolio2, pp3 Portfolio3}
strategy_id -> {pp1 2, pp2 2, pp3 1}
client_id -> {pp1 1, pp2 2, pp3 2}
strategy -> {pp1 ps2, pp2 ps2, pp3 ps1}
}
####################################
schema Ref = literal : Ty {
entities
country
currency
asset
strategy
foreign_keys
country : currency -> country
strategy : asset -> strategy
attributes
id : country -> Integer
code name : country -> String
id strategy_id : asset -> Integer
name description : asset -> String
id : strategy -> Integer
name description : strategy -> String
id country_id : currency -> Integer
code name : currency -> String
observation_equations
forall c. c.country.id = c.country_id
forall a. a.strategy.id = a.strategy_id
}
instance RefInstance = literal : Ref {
generators
rcty1 rcty2 rcty3 rcty4 rcty5 rcty6 : country
rcur1 rcur2 rcur3 : currency
ra1 ra2 ra3 ra4 ra5 ra6 ra7 ra8 ra9 ra10 : asset
rs1 rs2 rs3 rs4 rs5 : strategy
multi_equations
country -> {rcur1 rcty6, rcur2 rcty4, rcur3 rcty5}
country_id -> {rcur1 6, rcur2 4, rcur3 5}
strategy -> {ra1 rs1, ra2 rs3, ra3 rs2, ra4 rs4, ra5 rs5, ra6 rs4, ra7 rs4, ra8 rs5, ra9 rs3, ra10 rs1}
strategy_id -> {ra1 1, ra2 3, ra3 2, ra4 4, ra5 5, ra6 4, ra7 4, ra8 5, ra9 3, ra10 1}
id -> {rcty1 1, rcty2 2, rcty3 3, rcty4 4, rcty5 5, rcty6 6, rcur1 1, rcur2 2, rcur3 3, ra1 1, ra2 2, ra3 3, ra4 4, ra5 5, ra6 6, ra7 7, ra8 8, ra9 9, ra10 10, rs1 1, rs2 2, rs3 3, rs4 4, rs5 5}
code -> {rcty1 AU, rcty2 CH, rcty3 CN, rcty4 EU, rcty5 JP, rcty6 US, rcur1 USD, rcur2 EUR, rcur3 JPY}
name -> {ra1 A1, ra2 A2, ra3 A3, ra4 A4, ra5 A5, ra6 A6, ra7 A7, ra8 A8, ra9 A9, ra10 A10, rs1 Strat1, rs2 Strat2, rs3 Strat3, rs4 Strat4, rs5 Strat5, rcty1 Australia, rcty2 Switzerland, rcty3 China, rcty4 "European Union", rcty5 Japan, rcty6 "United States of America", rcur1 "US Dollar", rcur2 "Euro", rcur3 "Japanese Yen" }
description -> {ra1 Asset1, ra2 Asset2, ra3 Asset3, ra4 Asset4, ra5 Asset5, ra6 Asset6, ra7 Asset7, ra8 Asset8, ra9 Asset9, ra10 Asset10, rs1 Strategy1, rs2 Strategy2, rs3 Strategy3, rs4 Strategy4, rs5 Strategy5}
}
####################################
schema Trans = literal : Ty {
entities
asset
currency
transaction
foreign_keys
asset : transaction -> asset
currency : transaction -> currency
attributes
id : asset -> Integer
name description : asset -> String
id : currency -> Integer
code name : currency -> String
id asset_id portfolio_id quantity currency_id : transaction -> Integer
date : transaction -> Date # added as a new column
buy_sell_ind : transaction -> Boolean
price : transaction -> Double
observation_equations
forall t. t.asset.id = t.asset_id
forall t. t.currency.id = t.currency_id
}
instance TransInstance = literal : Trans {
generators
ta1 ta3 ta5 ta7 ta9 : asset
tc1 tc2 tc3 : currency
tt1 tt2 tt3 tt4 tt5 tt6 : transaction
multi_equations
id -> {ta1 1, ta3 3, ta5 5, ta7 7, ta9 9, tc1 1, tc2 2, tc3 3, tt1 1, tt2 2, tt3 3, tt4 4, tt5 5, tt6 6}
name -> {ta1 A1, ta3 A3, ta5 A5, ta7 A7, ta9 A9, tc1 "US Dollar", tc2 Euro, tc3 "Japanese Yen"}
description -> {ta1 Asset1, ta3 Asset3, ta5 Asset5, ta7 Asset7, ta9 Asset9}
code -> {tc1 USD, tc2 EUR, tc3 JPY}
asset -> {tt1 ta1, tt2 ta1, tt3 ta3, tt4 ta3, tt5 ta5, tt6 ta5}
asset_id -> {tt1 1, tt2 1, tt3 3, tt4 3, tt5 5, tt6 5}
portfolio_id -> {tt1 1, tt2 1, tt3 2, tt4 2, tt5 3, tt6 3}
buy_sell_ind -> {tt1 buy, tt2 sell, tt3 buy, tt4 sell, tt5 buy, tt6 sell}
quantity -> {tt1 200, tt2 100, tt3 150, tt4 150, tt5 200, tt6 100}
price -> {tt1 "5.51", tt2 "5.5", tt3 "2.5", tt4 "3.5", tt5 "1.6", tt6 "1.5"}
date -> {tt1 "1/1/2011", tt2 "6/1/2011", tt3 "9/1/2013", tt4 "3/1/2014", tt5 "2/1/2013", tt6 "7/1/2013"}
currency -> {tt1 tc2, tt2 tc2, tt3 tc1, tt4 tc1, tt5 tc3, tt6 tc3}
currency_id -> {tt1 2, tt2 2, tt3 1, tt4 1, tt5 3, tt6 3}
}
####################################
schema HoldPos = literal : Ty {
entities
client
holding
position
foreign_keys
client : holding -> client
client : position -> client
attributes
no : client -> Integer # new name
nm desc : client -> String # new name
id client_no portfolio_id asset_id quantity : holding -> Integer
purchase_date begin_date end_date : holding -> Date
purchase_price : holding -> Double
currency_code : holding -> String # switched from _id to _code
id client_no asset_id quantity current_value cost_basis : position -> Integer # new name
current_value_currency_code cost_basis_currency_code : position -> String # switched from _id to _code
observation_equations
forall h:holding. h.client.no = h.client_no
forall p:position. p.client.no = p.client_no
}
instance HoldPosInstance = literal : HoldPos {
generators
hpc1 hpc2 : client
hph1 hph2 hph3 hph4 hph5 : holding
hpp1 hpp2 : position
multi_equations
no -> {hpc1 1, hpc2 2}
nm -> {hpc1 Tom, hpc2 Dick}
desc -> {hpc1 "Tom Client", hpc2 "Dick Client"}
id -> {hph1 1, hph2 2, hph3 3, hph4 4, hph5 5, hpp1 1, hpp2 2}
client_no -> {hph1 1, hph2 1, hph3 2, hph4 2, hph5 2, hpp1 1, hpp2 2}
client -> {hph1 hpc1, hph2 hpc1, hph3 hpc2, hph4 hpc2, hph5 hpc2, hpp1 hpc1, hpp2 hpc2}
portfolio_id -> {hph1 1, hph2 1, hph3 3, hph4 3, hph5 3}
asset_id -> {hph1 1, hph2 1, hph3 3, hph4 5, hph5 5}
quantity -> {hph1 200, hph2 100, hph3 150, hph4 200, hph5 100, hpp1 100, hpp2 100}
purchase_date -> {hph1 "1/1/2011", hph2 "1/1/2011", hph3 "9/1/2013", hph4 "2/1/2013", hph5 "2/1/2013"}
begin_date -> {hph1 "1/1/2011", hph2 "6/1/2011", hph3 "9/1/2013", hph4 "2/1/2013", hph5 "7/1/2013"}
end_date -> {hph1 "6/1/2011", hph3 "3/1/2014", hph4 "7/1/2013"}
purchase_price -> {hph1 "5.51", hph2 "5.1", hph3 "2.5", hph4 "1.6", hph5 "1.6"}
currency_code -> {hph1 EUR, hph2 EUR, hph3 JPY, hph4 USD, hph5 USD}
asset_id -> {hpp1 1, hpp2 5}
current_value -> {hpp1 550, hpp2 170}
cost_basis -> {hpp1 551, hpp2 160}
current_value_currency_code -> {hpp1 EUR, hpp2 JPY}
cost_basis_currency_code -> {hpp1 EUR, hpp2 JPY}
}
####################################
schema_colimit Colimit = quotient Client + Ref + Trans + Portfolio + HoldPos : Ty {
entity_equations
Client.client = HoldPos.client
Ref.asset = Trans.asset
Ref.currency = Trans.currency
Ref.strategy = Portfolio.strategy
observation_equations
forall x. x.Client_client_id = x.HoldPos_client_no
forall x. x.Client_client_name = x.HoldPos_client_nm
forall x. x.Client_client_description = x.HoldPos_client_desc
forall x. x.Ref_strategy_id = x.Portfolio_strategy_id
forall x. x.Ref_strategy_name = x.Portfolio_strategy_name
forall x. x.Ref_strategy_description = x.Portfolio_strategy_description
forall x. x.Ref_asset_id = x.Trans_asset_id
forall x. x.Ref_asset_name = x.Trans_asset_name
forall x. x.Ref_asset_description = x.Trans_asset_description
forall x. x.Ref_currency_id = x.Trans_currency_id
forall x. x.Ref_currency_code = x.Trans_currency_code
forall x. x.Ref_currency_name = x.Trans_currency_name
options
simplify_names=false
}
instance ClientFwd = sigma (getMapping Colimit Client) ClientInstance
instance PortfolioFwd = sigma (getMapping Colimit Portfolio) PortfolioInstance
instance RefFwd = sigma (getMapping Colimit Ref) RefInstance
instance TransFwd = sigma (getMapping Colimit Trans) TransInstance
instance HoldPosFwd = sigma (getMapping Colimit HoldPos) HoldPosInstance
instance CoProd = coproduct ClientFwd + PortfolioFwd + RefFwd + TransFwd + HoldPosFwd : getSchema Colimit
####################################
instance E = quotient_query CoProd {
entity Client_client -> {
from
a:Client_client b:Client_client
where
a.Client_client_id = b.HoldPos_client_no
}
entity Ref_asset -> {
from
a:Ref_asset b:Ref_asset
where
a.Ref_asset_id = b.Trans_asset_id
}
entity Ref_currency -> {
from
a:Ref_currency b:Ref_currency
where
a.Ref_currency_id = b.Trans_currency_id
}
entity Ref_strategy -> {
from
a:Ref_strategy b:Ref_strategy
where
a.Portfolio_strategy_id = b.Ref_strategy_id
}
options
quotient_use_chase = false
}
####################################
schema Target = literal : Ty {
entities
currency #Trans Ref
strategy #Portfolio Ref
asset #Trans Ref
transaction #Trans
client #HoldPos
position #HoldPos
country #Ref
portfolioholding #NEW
portfolio #Portfolio
holding #HoldPos
foreign_keys
portfolio_strategy : portfolio -> strategy #Portfolio
parent : portfolio -> portfolio
position_client : position -> client #HoldPos
position_asset : position -> asset #NEW
position_current_value_currency : position -> currency #NEW
position_cost_basis_currency : position -> currency #NEW
asset_strategy : asset -> strategy #Ref
currency_country : currency -> country #Ref
transaction_currency : transaction -> currency #Trans
transaction_asset : transaction -> asset #Trans
transaction_portfolio : transaction -> portfolio #NEW
holding_currency : holding -> currency #NEW
holding_asset : holding -> asset
portfolioholding_holding : portfolioholding -> holding #NEW
portfolioholding_client : portfolioholding -> client #NEW
portfolioholding_portfolio : portfolioholding -> portfolio #NEW
path_equations
portfolio.parent.parent = portfolio.parent
attributes
code : currency -> String #Trans Ref
name : currency -> String #Trans Ref
id : currency -> Integer #Ref
country_id : currency -> Integer #Ref
code : country -> String #Ref
name : country -> String #Ref
id : country -> Integer #Ref
id : asset -> Integer #Trans Ref
description : asset -> String #Trans Ref
name : asset -> String #Trans Ref
strategy_id : asset -> Integer #Ref
name : strategy -> String #Ref Portfolio
description : strategy -> String #Ref Portfolio
id : strategy -> Integer #Ref Portfolio
name : portfolio -> String #Portfolio
description : portfolio -> String #Portfolio
id : portfolio -> Integer #Portfolio
strategy_id : portfolio -> Integer #Portfolio
parent_portfolio_id : portfolio -> Integer #Portfolio ADDED
quantity : position -> Integer #HoldPos
current_value : position -> Integer #HoldPos
cost_basis : position -> Integer #HoldPos
id : position -> Integer #HoldPos
client_id : position -> Integer #HoldPos
asset_id : position -> Integer #HoldPos
current_value_currency_id : position -> Integer # HoldPos
cost_basis_currency_id : position -> Integer # HoldPos
id : holding -> Integer #HoldPos
asset_id : holding -> Integer #HoldPos
currency_id : holding -> Integer #HoldPos
purchase_price : holding -> Double #HoldPos
quantity : holding -> Integer #HoldPos #HoldPos
purchase_date : holding -> Date #HoldPos
begin_date : holding -> Date #HoldPos new attribute
end_date : holding -> Date #HoldPos new attribute
description : client -> String #HoldPos
name : client -> String #HoldPos
id : client -> Integer #HoldPos
id : transaction -> Integer #Trans
asset_id : transaction -> Integer #Trans
buy_sell_ind : transaction -> Boolean #Trans
quantity : transaction -> Integer #Trans
price : transaction -> Double #Trans
currency_id : transaction -> Integer #Trans
portfolio_id : transaction -> Integer #Trans
date : transaction -> Date #added as a new column
id : portfolioholding -> Integer #NEW todo: talk to Jee about this
holding_id : portfolioholding -> Integer #NEW
client_id : portfolioholding -> Integer #NEW
portfolio_id : portfolioholding -> Integer #NEW
observation_equations
forall p. p.parent.id = p.parent_portfolio_id #added
forall p. p.portfolio_strategy.id = p.strategy_id
forall a. a.asset_strategy.id = a.strategy_id
forall p. p.position_client.id = p.client_id
forall c. c.currency_country.id = c.country_id
forall t. t.transaction_currency.id = t.currency_id
forall t. t.transaction_asset.id = t.asset_id
forall p. p.portfolioholding_holding.id = p.holding_id
forall p. p.portfolioholding_client.id = p.client_id
forall p. p.portfolioholding_portfolio.id = p.portfolio_id
forall p. p.position_asset.id = p.asset_id
forall h. h.holding_currency.id = h.currency_id
forall t. t.transaction_portfolio.id = t.portfolio_id
forall p. p.position_current_value_currency.id = p.current_value_currency_id
forall p. p.position_cost_basis_currency.id = p.cost_basis_currency_id
}
####################################
query ETL = literal : getSchema Colimit -> Target {
entity currency -> {
from
c:Ref_currency
attributes
code -> c.Ref_currency_code
name -> c.Ref_currency_name
id -> c.Ref_currency_id
country_id -> c.Ref_currency_country_id
foreign_keys
currency_country -> {c -> c.Ref_currency_country}
}
entity strategy -> {
from
s:Ref_strategy
attributes
name -> s.Ref_strategy_name
description -> s.Ref_strategy_description
id -> s.Ref_strategy_id
}
entity asset -> {
from
a:Ref_asset
attributes
id -> a.Ref_asset_id
description -> a.Ref_asset_description
name -> a.Ref_asset_name
strategy_id -> a.Ref_asset_strategy_id
foreign_keys
asset_strategy -> {s -> a.Ref_asset_strategy}
}
entity client -> {
from
c:Client_client
attributes
description -> c.Client_client_description
name -> c.Client_client_name
id -> c.Client_client_id
}
entity country -> {
from
c:Ref_country
attributes
code -> c.Ref_country_code
name -> c.Ref_country_name
id -> c.Ref_country_id
}
entity transaction -> {
from
t:Trans_transaction p:Portfolio_portfolio c:Client_client c1:Client_client
where
t.Trans_transaction_portfolio_id = p.Portfolio_portfolio_id
attributes
id -> t.Trans_transaction_id
asset_id -> t.Trans_transaction_asset_id
buy_sell_ind -> t.Trans_transaction_buy_sell_ind
quantity -> t.Trans_transaction_quantity
price -> t.Trans_transaction_price
date -> t.Trans_transaction_date
currency_id -> t.Trans_transaction_currency_id
portfolio_id -> t.Trans_transaction_portfolio_id
foreign_keys
transaction_currency -> {c -> t.Trans_transaction_currency}
transaction_asset -> {a -> t.Trans_transaction_asset}
transaction_portfolio -> {p -> p} #NEW
}
entity position -> {
from
p:HoldPos_position a:Ref_asset cv:Ref_currency cb:Ref_currency
where
p.HoldPos_position_asset_id = a.Ref_asset_id
p.HoldPos_position_current_value_currency_code = cv.Ref_currency_code
p.HoldPos_position_cost_basis_currency_code = cb.Ref_currency_code
attributes
quantity -> p.HoldPos_position_quantity
current_value -> p.HoldPos_position_current_value
cost_basis -> p.HoldPos_position_cost_basis
id -> p.HoldPos_position_id
client_id -> p.HoldPos_position_client_no
asset_id -> p.HoldPos_position_asset_id
current_value_currency_id -> cv.Ref_currency_id
cost_basis_currency_id -> cb.Ref_currency_id
foreign_keys
position_client -> {c -> p.HoldPos_position_client}
position_asset -> {a -> a} #NEW
position_current_value_currency -> {c -> cv} #NEW
position_cost_basis_currency -> {c -> cb} #NEW
}
entity portfolio -> {
from
p:Portfolio_portfolio
attributes
parent_portfolio_id -> p.Portfolio_portfolio_parent_id #added
name -> p.Portfolio_portfolio_name
description -> p.Portfolio_portfolio_description
id -> p.Portfolio_portfolio_id
strategy_id -> p.Portfolio_portfolio_strategy_id
foreign_keys
parent -> {p -> p.Portfolio_portfolio_parent} #added
portfolio_strategy -> {s -> p.Portfolio_portfolio_strategy}
}
entity holding -> {
from
h:HoldPos_holding c:Ref_currency a:Ref_asset
where
a.Ref_asset_id = h.HoldPos_holding_asset_id
h.HoldPos_holding_currency_code = c.Ref_currency_code
attributes
id -> h.HoldPos_holding_id
asset_id -> h.HoldPos_holding_asset_id
currency_id -> c.Ref_currency_id
purchase_price -> h.HoldPos_holding_purchase_price
quantity -> h.HoldPos_holding_quantity
purchase_date -> h.HoldPos_holding_purchase_date
begin_date -> h.HoldPos_holding_begin_date
end_date -> h.HoldPos_holding_end_date
foreign_keys
holding_asset -> {a -> a}
holding_currency -> {c -> c}
}
entity portfolioholding -> {
from
h:HoldPos_holding p:Portfolio_portfolio cur: Ref_currency c1:Client_client a:Ref_asset
where
a.Ref_asset_id = h.HoldPos_holding_asset_id
p.Portfolio_portfolio_parent.Portfolio_portfolio_client_id = c1.Client_client_id
c1.Client_client_id = h.HoldPos_holding_client.Client_client_id
h.HoldPos_holding_currency_code = cur.Ref_currency_code
p.Portfolio_portfolio_client_id = h.HoldPos_holding_client.Client_client_id
h.HoldPos_holding_portfolio_id = p.Portfolio_portfolio_id
attributes
id -> keygen(h.HoldPos_holding_id, h.HoldPos_holding_client.Client_client_id, p.Portfolio_portfolio_id) #added by ryan
holding_id -> h.HoldPos_holding_id
client_id -> c1.Client_client_id
portfolio_id -> p.Portfolio_portfolio_id
foreign_keys
portfolioholding_holding -> {h -> h c -> cur a -> a}
portfolioholding_client -> {c -> h.HoldPos_holding_client}
portfolioholding_portfolio -> {p -> p}
}
}
instance Output = distinct eval ETL E
Keywords:
typeside_literal
sigma
quotient_query
schema_literal
quotient
query_literal
instance_literal
distinct
coproduct
Options:
quotient_use_chase
simplify_names
instance ClientInstance
clientID | id | name | description |
---|
0 | 1 | Tom | Tom Client |
1 | 2 | Dick | Dick Client |
2 | 3 | Harry | Harry Client |
instance RefInstance
assetID | id | strategy_id | name | description | strategy |
---|
0 | 1 | 1 | A1 | Asset1 | 19 |
1 | 2 | 3 | A2 | Asset2 | 20 |
2 | 3 | 2 | A3 | Asset3 | 21 |
3 | 4 | 4 | A4 | Asset4 | 22 |
4 | 5 | 5 | A5 | Asset5 | 23 |
5 | 6 | 4 | A6 | Asset6 | 22 |
6 | 7 | 4 | A7 | Asset7 | 22 |
7 | 8 | 5 | A8 | Asset8 | 23 |
8 | 9 | 3 | A9 | Asset9 | 20 |
9 | 10 | 1 | A10 | Asset10 | 19 |
countryID | id | code | name |
---|
10 | 1 | AU | Australia |
11 | 2 | CH | Switzerland |
12 | 3 | CN | China |
13 | 4 | EU | European Union |
14 | 5 | JP | Japan |
15 | 6 | US | United States of America |
currencyID | id | country_id | code | name | country |
---|
16 | 1 | 6 | USD | US Dollar | 15 |
17 | 2 | 4 | EUR | Euro | 13 |
18 | 3 | 5 | JPY | Japanese Yen | 14 |
strategyID | id | name | description |
---|
19 | 1 | Strat1 | Strategy1 |
20 | 3 | Strat3 | Strategy3 |
21 | 2 | Strat2 | Strategy2 |
22 | 4 | Strat4 | Strategy4 |
23 | 5 | Strat5 | Strategy5 |
instance PortfolioInstance
portfolioID | id | name | description | client_id | strategy_id | parent_id | parent | strategy |
---|
0 | 1 | Port1 | Portfolio1 | 1 | 2 | 1 | 0 | 4 |
1 | 2 | Port2 | Portfolio2 | 2 | 2 | 2 | 1 | 4 |
2 | 3 | Port3 | Portfolio3 | 2 | 1 | 2 | 1 | 3 |
strategyID | id | name | description |
---|
3 | 1 | Strat1 | Strategy1 |
4 | 2 | Strat2 | Strategy2 |
5 | 3 | Strat3 | Strategy3 |
instance TransInstance
assetID | id | name | description |
---|
0 | 1 | A1 | Asset1 |
1 | 3 | A3 | Asset3 |
2 | 5 | A5 | Asset5 |
3 | 7 | A7 | Asset7 |
4 | 9 | A9 | Asset9 |
currencyID | id | code | name |
---|
5 | 1 | USD | US Dollar |
6 | 2 | EUR | Euro |
7 | 3 | JPY | Japanese Yen |
transactionID | id | asset_id | portfolio_id | quantity | currency_id | date | buy_sell_ind | price | asset | currency |
---|
8 | 1 | 1 | 1 | 200 | 2 | 1/1/2011 | buy | 5.51 | 0 | 6 |
9 | 2 | 1 | 1 | 100 | 2 | 6/1/2011 | sell | 5.5 | 0 | 6 |
10 | 3 | 3 | 2 | 150 | 1 | 9/1/2013 | buy | 2.5 | 1 | 5 |
11 | 4 | 3 | 2 | 150 | 1 | 3/1/2014 | sell | 3.5 | 1 | 5 |
12 | 5 | 5 | 3 | 200 | 3 | 2/1/2013 | buy | 1.6 | 2 | 7 |
13 | 6 | 5 | 3 | 100 | 3 | 7/1/2013 | sell | 1.5 | 2 | 7 |
instance HoldPosInstance
clientID | no | nm | desc |
---|
0 | 1 | Tom | Tom Client |
1 | 2 | Dick | Dick Client |
holdingID | id | client_no | portfolio_id | asset_id | quantity | purchase_date | begin_date | end_date | purchase_price | currency_code | client |
---|
2 | 1 | 1 | 1 | 1 | 200 | 1/1/2011 | 1/1/2011 | 6/1/2011 | 5.51 | EUR | 0 |
3 | 2 | 1 | 1 | 1 | 100 | 1/1/2011 | 6/1/2011 | ?0 | 5.1 | EUR | 0 |
4 | 3 | 2 | 3 | 3 | 150 | 9/1/2013 | 9/1/2013 | 3/1/2014 | 2.5 | JPY | 1 |
5 | 4 | 2 | 3 | 5 | 200 | 2/1/2013 | 2/1/2013 | 7/1/2013 | 1.6 | USD | 1 |
6 | 5 | 2 | 3 | 5 | 100 | 2/1/2013 | 7/1/2013 | ?1 | 1.6 | USD | 1 |
positionID | id | client_no | asset_id | quantity | current_value | cost_basis | current_value_currency_code | cost_basis_currency_code | client |
---|
7 | 1 | 1 | 1 | 100 | 550 | 551 | EUR | EUR | 0 |
8 | 2 | 2 | 5 | 100 | 170 | 160 | JPY | JPY | 1 |
instance PortfolioFwd
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
0 | Port1 | Portfolio1 | 1 | 2 | 1 | 1 | 0 | 4 |
1 | Port2 | Portfolio2 | 2 | 2 | 2 | 2 | 1 | 4 |
2 | Port3 | Portfolio3 | 3 | 1 | 2 | 2 | 1 | 3 |
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
3 | Strategy1 | 1 | Strat1 | Strategy1 | Strat1 | 1 |
4 | Strategy2 | 2 | Strat2 | Strategy2 | Strat2 | 2 |
5 | Strategy3 | 3 | Strat3 | Strategy3 | Strat3 | 3 |
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
instance ClientFwd
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
0 | Tom Client | Tom | 1 | 1 | Tom | Tom Client |
1 | Dick Client | Dick | 2 | 2 | Dick | Dick Client |
2 | Harry Client | Harry | 3 | 3 | Harry | Harry Client |
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
instance HoldPosFwd
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
0 | Tom Client | Tom | 1 | 1 | Tom | Tom Client |
1 | Dick Client | Dick | 2 | 2 | Dick | Dick Client |
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
2 | 6/1/2011 | EUR | 1 | 1/1/2011 | 5.51 | 1 | 1 | 1/1/2011 | 200 | 1 | 0 |
3 | ?0 | EUR | 2 | 1/1/2011 | 5.1 | 1 | 1 | 6/1/2011 | 100 | 1 | 0 |
4 | 3/1/2014 | JPY | 3 | 9/1/2013 | 2.5 | 3 | 3 | 9/1/2013 | 150 | 2 | 1 |
5 | 7/1/2013 | USD | 4 | 2/1/2013 | 1.6 | 3 | 5 | 2/1/2013 | 200 | 2 | 1 |
6 | ?1 | USD | 5 | 2/1/2013 | 1.6 | 3 | 5 | 7/1/2013 | 100 | 2 | 1 |
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
7 | 1 | EUR | EUR | 550 | 100 | 1 | 1 | 551 | 0 |
8 | 2 | JPY | JPY | 170 | 100 | 5 | 2 | 160 | 1 |
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
instance TransFwd
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
0 | 1 | A1 | Asset1 | ?0 | A1 | 1 | Asset1 | 11 |
1 | 3 | A3 | Asset3 | ?1 | A3 | 3 | Asset3 | 12 |
2 | 5 | A5 | Asset5 | ?2 | A5 | 5 | Asset5 | 13 |
3 | 7 | A7 | Asset7 | ?3 | A7 | 7 | Asset7 | 14 |
4 | 9 | A9 | Asset9 | ?4 | A9 | 9 | Asset9 | 15 |
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
5 | ?5 | ?6 | ?7 |
6 | ?8 | ?9 | ?10 |
7 | ?11 | ?12 | ?13 |
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
8 | 1 | USD | ?7 | 1 | US Dollar | USD | US Dollar | 5 |
9 | 2 | EUR | ?10 | 2 | Euro | EUR | Euro | 6 |
10 | 3 | JPY | ?13 | 3 | Japanese Yen | JPY | Japanese Yen | 7 |
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
11 | ?14 | ?0 | ?15 | ?14 | ?15 | ?0 |
12 | ?16 | ?1 | ?17 | ?16 | ?17 | ?1 |
13 | ?18 | ?2 | ?19 | ?18 | ?19 | ?2 |
14 | ?20 | ?3 | ?21 | ?20 | ?21 | ?3 |
15 | ?22 | ?4 | ?23 | ?22 | ?23 | ?4 |
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
16 | 1 | 5.51 | buy | 1/1/2011 | 200 | 1 | 1 | 2 | 0 | 9 |
17 | 2 | 5.5 | sell | 6/1/2011 | 100 | 1 | 1 | 2 | 0 | 9 |
18 | 3 | 2.5 | buy | 9/1/2013 | 150 | 3 | 2 | 1 | 1 | 8 |
19 | 4 | 3.5 | sell | 3/1/2014 | 150 | 3 | 2 | 1 | 1 | 8 |
20 | 5 | 1.6 | buy | 2/1/2013 | 200 | 5 | 3 | 3 | 2 | 10 |
21 | 6 | 1.5 | sell | 7/1/2013 | 100 | 5 | 3 | 3 | 2 | 10 |
instance RefFwd
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
0 | 1 | A1 | Asset1 | 1 | A1 | 1 | Asset1 | 19 |
1 | 2 | A2 | Asset2 | 3 | A2 | 2 | Asset2 | 20 |
2 | 3 | A3 | Asset3 | 2 | A3 | 3 | Asset3 | 21 |
3 | 4 | A4 | Asset4 | 4 | A4 | 4 | Asset4 | 22 |
4 | 5 | A5 | Asset5 | 5 | A5 | 5 | Asset5 | 23 |
5 | 6 | A6 | Asset6 | 4 | A6 | 6 | Asset6 | 22 |
6 | 7 | A7 | Asset7 | 4 | A7 | 7 | Asset7 | 22 |
7 | 8 | A8 | Asset8 | 5 | A8 | 8 | Asset8 | 23 |
8 | 9 | A9 | Asset9 | 3 | A9 | 9 | Asset9 | 20 |
9 | 10 | A10 | Asset10 | 1 | A10 | 10 | Asset10 | 19 |
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
10 | AU | Australia | 1 |
11 | CH | Switzerland | 2 |
12 | CN | China | 3 |
13 | EU | European Union | 4 |
14 | JP | Japan | 5 |
15 | US | United States of America | 6 |
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
16 | 1 | USD | 6 | 1 | US Dollar | USD | US Dollar | 15 |
17 | 2 | EUR | 4 | 2 | Euro | EUR | Euro | 13 |
18 | 3 | JPY | 5 | 3 | Japanese Yen | JPY | Japanese Yen | 14 |
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
19 | Strategy1 | 1 | Strat1 | Strategy1 | Strat1 | 1 |
20 | Strategy3 | 3 | Strat3 | Strategy3 | Strat3 | 3 |
21 | Strategy2 | 2 | Strat2 | Strategy2 | Strat2 | 2 |
22 | Strategy4 | 4 | Strat4 | Strategy4 | Strat4 | 4 |
23 | Strategy5 | 5 | Strat5 | Strategy5 | Strat5 | 5 |
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
instance CoProd
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
0 | Tom Client | Tom | 1 | 1 | Tom | Tom Client |
1 | Dick Client | Dick | 2 | 2 | Dick | Dick Client |
2 | Harry Client | Harry | 3 | 3 | Harry | Harry Client |
3 | Tom Client | Tom | 1 | 1 | Tom | Tom Client |
4 | Dick Client | Dick | 2 | 2 | Dick | Dick Client |
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
5 | 6/1/2011 | EUR | 1 | 1/1/2011 | 5.51 | 1 | 1 | 1/1/2011 | 200 | 1 | 3 |
6 | ?0 | EUR | 2 | 1/1/2011 | 5.1 | 1 | 1 | 6/1/2011 | 100 | 1 | 3 |
7 | 3/1/2014 | JPY | 3 | 9/1/2013 | 2.5 | 3 | 3 | 9/1/2013 | 150 | 2 | 4 |
8 | 7/1/2013 | USD | 4 | 2/1/2013 | 1.6 | 3 | 5 | 2/1/2013 | 200 | 2 | 4 |
9 | ?1 | USD | 5 | 2/1/2013 | 1.6 | 3 | 5 | 7/1/2013 | 100 | 2 | 4 |
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
10 | 1 | EUR | EUR | 550 | 100 | 1 | 1 | 551 | 3 |
11 | 2 | JPY | JPY | 170 | 100 | 5 | 2 | 160 | 4 |
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
12 | Port1 | Portfolio1 | 1 | 2 | 1 | 1 | 12 | 46 |
13 | Port2 | Portfolio2 | 2 | 2 | 2 | 2 | 13 | 46 |
14 | Port3 | Portfolio3 | 3 | 1 | 2 | 2 | 13 | 45 |
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
15 | 1 | A1 | Asset1 | 1 | A1 | 1 | Asset1 | 48 |
16 | 2 | A2 | Asset2 | 3 | A2 | 2 | Asset2 | 49 |
17 | 3 | A3 | Asset3 | 2 | A3 | 3 | Asset3 | 50 |
18 | 4 | A4 | Asset4 | 4 | A4 | 4 | Asset4 | 51 |
19 | 5 | A5 | Asset5 | 5 | A5 | 5 | Asset5 | 52 |
20 | 6 | A6 | Asset6 | 4 | A6 | 6 | Asset6 | 51 |
21 | 7 | A7 | Asset7 | 4 | A7 | 7 | Asset7 | 51 |
22 | 8 | A8 | Asset8 | 5 | A8 | 8 | Asset8 | 52 |
23 | 9 | A9 | Asset9 | 3 | A9 | 9 | Asset9 | 49 |
24 | 10 | A10 | Asset10 | 1 | A10 | 10 | Asset10 | 48 |
25 | 1 | A1 | Asset1 | ?2 | A1 | 1 | Asset1 | 53 |
26 | 3 | A3 | Asset3 | ?3 | A3 | 3 | Asset3 | 54 |
27 | 5 | A5 | Asset5 | ?4 | A5 | 5 | Asset5 | 55 |
28 | 7 | A7 | Asset7 | ?5 | A7 | 7 | Asset7 | 56 |
29 | 9 | A9 | Asset9 | ?6 | A9 | 9 | Asset9 | 57 |
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
30 | AU | Australia | 1 |
31 | CH | Switzerland | 2 |
32 | CN | China | 3 |
33 | EU | European Union | 4 |
34 | JP | Japan | 5 |
35 | US | United States of America | 6 |
36 | ?7 | ?8 | ?9 |
37 | ?10 | ?11 | ?12 |
38 | ?13 | ?14 | ?15 |
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
39 | 1 | USD | 6 | 1 | US Dollar | USD | US Dollar | 35 |
40 | 2 | EUR | 4 | 2 | Euro | EUR | Euro | 33 |
41 | 3 | JPY | 5 | 3 | Japanese Yen | JPY | Japanese Yen | 34 |
42 | 1 | USD | ?9 | 1 | US Dollar | USD | US Dollar | 36 |
43 | 2 | EUR | ?12 | 2 | Euro | EUR | Euro | 37 |
44 | 3 | JPY | ?15 | 3 | Japanese Yen | JPY | Japanese Yen | 38 |
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
45 | Strategy1 | 1 | Strat1 | Strategy1 | Strat1 | 1 |
46 | Strategy2 | 2 | Strat2 | Strategy2 | Strat2 | 2 |
47 | Strategy3 | 3 | Strat3 | Strategy3 | Strat3 | 3 |
48 | Strategy1 | 1 | Strat1 | Strategy1 | Strat1 | 1 |
49 | Strategy3 | 3 | Strat3 | Strategy3 | Strat3 | 3 |
50 | Strategy2 | 2 | Strat2 | Strategy2 | Strat2 | 2 |
51 | Strategy4 | 4 | Strat4 | Strategy4 | Strat4 | 4 |
52 | Strategy5 | 5 | Strat5 | Strategy5 | Strat5 | 5 |
53 | ?16 | ?2 | ?17 | ?16 | ?17 | ?2 |
54 | ?18 | ?3 | ?19 | ?18 | ?19 | ?3 |
55 | ?20 | ?4 | ?21 | ?20 | ?21 | ?4 |
56 | ?22 | ?5 | ?23 | ?22 | ?23 | ?5 |
57 | ?24 | ?6 | ?25 | ?24 | ?25 | ?6 |
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
58 | 1 | 5.51 | buy | 1/1/2011 | 200 | 1 | 1 | 2 | 25 | 43 |
59 | 2 | 5.5 | sell | 6/1/2011 | 100 | 1 | 1 | 2 | 25 | 43 |
60 | 3 | 2.5 | buy | 9/1/2013 | 150 | 3 | 2 | 1 | 26 | 42 |
61 | 4 | 3.5 | sell | 3/1/2014 | 150 | 3 | 2 | 1 | 26 | 42 |
62 | 5 | 1.6 | buy | 2/1/2013 | 200 | 5 | 3 | 3 | 27 | 44 |
63 | 6 | 1.5 | sell | 7/1/2013 | 100 | 5 | 3 | 3 | 27 | 44 |
instance E
Client_clientID | Client_client_description | Client_client_name | HoldPos_client_no | Client_client_id | HoldPos_client_nm | HoldPos_client_desc |
---|
0 | Tom Client | Tom | 1 | 1 | Tom | Tom Client |
1 | Dick Client | Dick | 2 | 2 | Dick | Dick Client |
2 | Harry Client | Harry | 3 | 3 | Harry | Harry Client |
HoldPos_holdingID | HoldPos_holding_end_date | HoldPos_holding_currency_code | HoldPos_holding_id | HoldPos_holding_purchase_date | HoldPos_holding_purchase_price | HoldPos_holding_portfolio_id | HoldPos_holding_asset_id | HoldPos_holding_begin_date | HoldPos_holding_quantity | HoldPos_holding_client_no | HoldPos_holding_client |
---|
3 | 6/1/2011 | EUR | 1 | 1/1/2011 | 5.51 | 1 | 1 | 1/1/2011 | 200 | 1 | 0 |
4 | ?0 | EUR | 2 | 1/1/2011 | 5.1 | 1 | 1 | 6/1/2011 | 100 | 1 | 0 |
5 | 3/1/2014 | JPY | 3 | 9/1/2013 | 2.5 | 3 | 3 | 9/1/2013 | 150 | 2 | 1 |
6 | 7/1/2013 | USD | 4 | 2/1/2013 | 1.6 | 3 | 5 | 2/1/2013 | 200 | 2 | 1 |
7 | ?1 | USD | 5 | 2/1/2013 | 1.6 | 3 | 5 | 7/1/2013 | 100 | 2 | 1 |
HoldPos_positionID | HoldPos_position_client_no | HoldPos_position_current_value_currency_code | HoldPos_position_cost_basis_currency_code | HoldPos_position_current_value | HoldPos_position_quantity | HoldPos_position_asset_id | HoldPos_position_id | HoldPos_position_cost_basis | HoldPos_position_client |
---|
8 | 1 | EUR | EUR | 550 | 100 | 1 | 1 | 551 | 0 |
9 | 2 | JPY | JPY | 170 | 100 | 5 | 2 | 160 | 1 |
Portfolio_portfolioID | Portfolio_portfolio_name | Portfolio_portfolio_description | Portfolio_portfolio_id | Portfolio_portfolio_strategy_id | Portfolio_portfolio_client_id | Portfolio_portfolio_parent_id | Portfolio_portfolio_parent | Portfolio_portfolio_strategy |
---|
10 | Port1 | Portfolio1 | 1 | 2 | 1 | 1 | 10 | 33 |
11 | Port2 | Portfolio2 | 2 | 2 | 2 | 2 | 11 | 33 |
12 | Port3 | Portfolio3 | 3 | 1 | 2 | 2 | 11 | 32 |
Ref_assetID | Trans_asset_id | Ref_asset_name | Ref_asset_description | Ref_asset_strategy_id | Trans_asset_name | Ref_asset_id | Trans_asset_description | Ref_asset_strategy |
---|
13 | 1 | A1 | Asset1 | 1 | A1 | 1 | Asset1 | 32 |
14 | 3 | A3 | Asset3 | 2 | A3 | 3 | Asset3 | 33 |
15 | 5 | A5 | Asset5 | 5 | A5 | 5 | Asset5 | 35 |
16 | 7 | A7 | Asset7 | 4 | A7 | 7 | Asset7 | 36 |
17 | 9 | A9 | Asset9 | 3 | A9 | 9 | Asset9 | 34 |
18 | 2 | A2 | Asset2 | 3 | A2 | 2 | Asset2 | 34 |
19 | 4 | A4 | Asset4 | 4 | A4 | 4 | Asset4 | 36 |
20 | 6 | A6 | Asset6 | 4 | A6 | 6 | Asset6 | 36 |
21 | 8 | A8 | Asset8 | 5 | A8 | 8 | Asset8 | 35 |
22 | 10 | A10 | Asset10 | 1 | A10 | 10 | Asset10 | 32 |
Ref_countryID | Ref_country_code | Ref_country_name | Ref_country_id |
---|
23 | US | United States of America | 6 |
24 | EU | European Union | 4 |
25 | JP | Japan | 5 |
26 | AU | Australia | 1 |
27 | CH | Switzerland | 2 |
28 | CN | China | 3 |
Ref_currencyID | Ref_currency_id | Ref_currency_code | Ref_currency_country_id | Trans_currency_id | Ref_currency_name | Trans_currency_code | Trans_currency_name | Ref_currency_country |
---|
29 | 1 | USD | 6 | 1 | US Dollar | USD | US Dollar | 23 |
30 | 2 | EUR | 4 | 2 | Euro | EUR | Euro | 24 |
31 | 3 | JPY | 5 | 3 | Japanese Yen | JPY | Japanese Yen | 25 |
Ref_strategyID | Ref_strategy_description | Portfolio_strategy_id | Portfolio_strategy_name | Portfolio_strategy_description | Ref_strategy_name | Ref_strategy_id |
---|
32 | Strategy1 | 1 | Strat1 | Strategy1 | Strat1 | 1 |
33 | Strategy2 | 2 | Strat2 | Strategy2 | Strat2 | 2 |
34 | Strategy3 | 3 | Strat3 | Strategy3 | Strat3 | 3 |
35 | Strategy5 | 5 | Strat5 | Strategy5 | Strat5 | 5 |
36 | Strategy4 | 4 | Strat4 | Strategy4 | Strat4 | 4 |
Trans_transactionID | Trans_transaction_id | Trans_transaction_price | Trans_transaction_buy_sell_ind | Trans_transaction_date | Trans_transaction_quantity | Trans_transaction_asset_id | Trans_transaction_portfolio_id | Trans_transaction_currency_id | Trans_transaction_asset | Trans_transaction_currency |
---|
37 | 1 | 5.51 | buy | 1/1/2011 | 200 | 1 | 1 | 2 | 13 | 30 |
38 | 2 | 5.5 | sell | 6/1/2011 | 100 | 1 | 1 | 2 | 13 | 30 |
39 | 3 | 2.5 | buy | 9/1/2013 | 150 | 3 | 2 | 1 | 14 | 29 |
40 | 4 | 3.5 | sell | 3/1/2014 | 150 | 3 | 2 | 1 | 14 | 29 |
41 | 5 | 1.6 | buy | 2/1/2013 | 200 | 5 | 3 | 3 | 15 | 31 |
42 | 6 | 1.5 | sell | 7/1/2013 | 100 | 5 | 3 | 3 | 15 | 31 |
instance Output
assetID | id | description | name | strategy_id | asset_strategy |
---|
0 | 1 | Asset1 | A1 | 1 | 37 |
1 | 3 | Asset3 | A3 | 2 | 38 |
2 | 5 | Asset5 | A5 | 5 | 40 |
3 | 7 | Asset7 | A7 | 4 | 41 |
4 | 9 | Asset9 | A9 | 3 | 39 |
5 | 2 | Asset2 | A2 | 3 | 39 |
6 | 4 | Asset4 | A4 | 4 | 41 |
7 | 6 | Asset6 | A6 | 4 | 41 |
8 | 8 | Asset8 | A8 | 5 | 40 |
9 | 10 | Asset10 | A10 | 1 | 37 |
clientID | description | name | id |
---|
10 | Tom Client | Tom | 1 |
11 | Dick Client | Dick | 2 |
12 | Harry Client | Harry | 3 |
countryID | code | name | id |
---|
13 | US | United States of America | 6 |
14 | EU | European Union | 4 |
15 | JP | Japan | 5 |
16 | AU | Australia | 1 |
17 | CH | Switzerland | 2 |
18 | CN | China | 3 |
currencyID | code | name | id | country_id | currency_country |
---|
19 | USD | US Dollar | 1 | 6 | 13 |
20 | EUR | Euro | 2 | 4 | 14 |
21 | JPY | Japanese Yen | 3 | 5 | 15 |
holdingID | id | asset_id | currency_id | purchase_price | quantity | purchase_date | begin_date | end_date | holding_asset | holding_currency |
---|
22 | 1 | 1 | 2 | 5.51 | 200 | 1/1/2011 | 1/1/2011 | 6/1/2011 | 0 | 20 |
23 | 2 | 1 | 2 | 5.1 | 100 | 1/1/2011 | 6/1/2011 | ?0 | 0 | 20 |
24 | 3 | 3 | 3 | 2.5 | 150 | 9/1/2013 | 9/1/2013 | 3/1/2014 | 1 | 21 |
25 | 4 | 5 | 1 | 1.6 | 200 | 2/1/2013 | 2/1/2013 | 7/1/2013 | 2 | 19 |
26 | 5 | 5 | 1 | 1.6 | 100 | 2/1/2013 | 7/1/2013 | ?1 | 2 | 19 |
portfolioID | name | description | id | strategy_id | parent_portfolio_id | parent | portfolio_strategy |
---|
27 | Port1 | Portfolio1 | 1 | 2 | 1 | 27 | 38 |
28 | Port2 | Portfolio2 | 2 | 2 | 2 | 28 | 38 |
29 | Port3 | Portfolio3 | 3 | 1 | 2 | 28 | 37 |
portfolioholdingID | id | holding_id | client_id | portfolio_id | portfolioholding_client | portfolioholding_holding | portfolioholding_portfolio |
---|
30 | 5 | 1 | 1 | 1 | 10 | 22 | 27 |
31 | 0 | 2 | 1 | 1 | 10 | 23 | 27 |
32 | 2 | 3 | 2 | 3 | 11 | 24 | 29 |
33 | 7 | 4 | 2 | 3 | 11 | 25 | 29 |
34 | 4 | 5 | 2 | 3 | 11 | 26 | 29 |
positionID | quantity | current_value | cost_basis | id | client_id | asset_id | current_value_currency_id | cost_basis_currency_id | position_asset | position_client | position_cost_basis_currency | position_current_value_currency |
---|
35 | 100 | 550 | 551 | 1 | 1 | 1 | 2 | 2 | 0 | 10 | 20 | 20 |
36 | 100 | 170 | 160 | 2 | 2 | 5 | 3 | 3 | 2 | 11 | 21 | 21 |
strategyID | name | description | id |
---|
37 | Strat1 | Strategy1 | 1 |
38 | Strat2 | Strategy2 | 2 |
39 | Strat3 | Strategy3 | 3 |
40 | Strat5 | Strategy5 | 5 |
41 | Strat4 | Strategy4 | 4 |
transactionID | id | asset_id | buy_sell_ind | quantity | price | currency_id | portfolio_id | date | transaction_asset | transaction_currency | transaction_portfolio |
---|
42 | 1 | 1 | buy | 200 | 5.51 | 2 | 1 | 1/1/2011 | 0 | 20 | 27 |
43 | 2 | 1 | sell | 100 | 5.5 | 2 | 1 | 6/1/2011 | 0 | 20 | 27 |
44 | 3 | 3 | buy | 150 | 2.5 | 1 | 2 | 9/1/2013 | 1 | 19 | 28 |
45 | 4 | 3 | sell | 150 | 3.5 | 1 | 2 | 3/1/2014 | 1 | 19 | 28 |
46 | 5 | 5 | buy | 200 | 1.6 | 3 | 3 | 2/1/2013 | 2 | 21 | 29 |
47 | 6 | 5 | sell | 100 | 1.5 | 3 | 3 | 7/1/2013 | 2 | 21 | 29 |