CA Console
Home
Corporate Actions
Dividends
Late Dividends
Stock Splits
Rights Issues
Spin-Offs
Stock Dividends
ID Changes
M&A Command Center
Configs
CA Configs
Late Div Configs
Dividend Settings
Schnellschuss Settings
Late Div Settings
Badge Rule Settings
Configuration
ID: 16
/ QAI North American Dividends
Open Builder
Back to List
YAML (Read-only)
All edits must be done in the Builder
source: type: sql sql: query: "SELECT G.Sedol,\nr.RIC,\nG.Isin,\nG.Name,\nG.Country,\nD.*,\nDT.*\nFROM\ \ [QAI].[dbo].SECMSTRX G\nJOIN [QAI].[dbo].SECMAPX M\nON M.SECCODE = G.SECCODE\n\ AND G.TYPE_ = 1 -- equity\nAND M.VENTYPE = 33\nJOIN [QAI].[dbo].DS2DIV D\nON\ \ D.INFOCODE = M.VENCODE\nJOIN [QAI].[dbo].DS2XREF DT\nON DT.CODE = D.DIVTYPECODE\n\ AND DT.TYPE_ = 8\njoin [QAI].[dbo].PermSecMapX mp -- map to Symbology\non mp.SecCode\ \ = g.SecCode\nand mp.RegCode = 1 -- North American security\nand mp.EntType\ \ = 55 -- quote level\njoin [QAI].[dbo].PermRICData r\non r.QuotePermID = mp.EntPermID\n\ and d.effectivedate between r.startdate and r.enddate -- get the RIC as of\ \ the effective date of the event\nWHERE \nD.EffectiveDate BETWEEN '{DATE_FROM}'\ \ AND '{DATE_TO}'\nAND \nr.RIC in {IDENTIFIERS}\nORDER BY D.EFFECTIVEDATE DESC;\n" date: from: yesterday to: today+15 format: '%Y-%m-%d' identifiers: enabled: true portfolio_identifier_in: Security RIC portfolio_column: Security RIC lookback_days: 5 vendor_key: QAI pipeline: - id: step-mhj9gmd5byf4xgr type: core_source name: Source config: type: sql sql: query: "SELECT G.Sedol,\nr.RIC,\nG.Isin,\nG.Name,\nG.Country,\nD.*,\nDT.*\n\ FROM [QAI].[dbo].SECMSTRX G\nJOIN [QAI].[dbo].SECMAPX M\nON M.SECCODE = G.SECCODE\n\ AND G.TYPE_ = 1 -- equity\nAND M.VENTYPE = 33\nJOIN [QAI].[dbo].DS2DIV D\n\ ON D.INFOCODE = M.VENCODE\nJOIN [QAI].[dbo].DS2XREF DT\nON DT.CODE = D.DIVTYPECODE\n\ AND DT.TYPE_ = 8\njoin [QAI].[dbo].PermSecMapX mp -- map to Symbology\non\ \ mp.SecCode = g.SecCode\nand mp.RegCode = 1 -- North American security\n\ and mp.EntType = 55 -- quote level\njoin [QAI].[dbo].PermRICData r\non r.QuotePermID\ \ = mp.EntPermID\nand d.effectivedate between r.startdate and r.enddate --\ \ get the RIC as of the effective date of the event\nWHERE \nD.EffectiveDate\ \ BETWEEN '{DATE_FROM}' AND '{DATE_TO}'\nAND \nr.RIC in {IDENTIFIERS}\nORDER\ \ BY D.EFFECTIVEDATE DESC;\n" date: from: yesterday to: today+15 format: '%Y-%m-%d' identifiers: enabled: true portfolio_identifier_in: Security RIC portfolio_column: Security RIC lookback_days: 5 vendor_key: QAI - id: step-mhno4zwx93aigt2 type: optional_compute name: Create QAI ID config: name: Create QAI ID outputs: &id002 QAI_ID: concat: sep: _ parts: - type: col value: RIC - type: col value: EventNum - id: step-mhnod876wdj9x9m type: optional_dedupe name: Get rid of duplicates config: name: Get rid of duplicates subset: QAI_ID keep: first - id: step-mhnp2bm6as1ae9h type: optional_compute name: Handle Gross & Net Dividends config: name: Handle Gross & Net Dividends outputs: &id003 GrossDivRate: if: condition: col: TaxMarker op: == value: G then: type: col value: DivRate else: type: literal value: Not Given by QAI NetDivRate: if: condition: col: TaxMarker op: == value: N then: type: col value: DivRate else: type: literal value: Not Given by QAI - id: step-mhj9gmd5z7vv73i type: core_identifier name: Identifier Mapping config: &id001 out_col: Security_RIC drop_unmapped: true chain: - method: RIC col: RIC - id: step-mhj9gmd5fnxro9u type: core_classify name: CA Classification config: target_col: Vendor_MV_ActionType rules: &id004 - when: '' set: REGULAR CASH DIVIDEND conditions: - col: Desc_ op: == value: Final - col: Desc_ op: == value: Interim - col: Desc_ op: == value: Yearly - col: Desc_ op: == value: Quarterly - col: Desc_ op: == value: Half yearly - col: Desc_ op: == value: Monthly - col: Desc_ op: == value: Broken period payment - col: Desc_ op: == value: Thrice yearly how: any mode: simple value: Thrice yearly col: Desc_ - when: '' set: SPECIAL CASH DIVIDEND conditions: - col: Desc_ op: == value: Special - col: Desc_ op: == value: Extra - col: Desc_ op: == value: Payment restricted to non-resident holders - col: Desc_ op: == value: Undefined capital gains payment - col: Desc_ op: == value: Capital gains payment - col: Desc_ op: == value: Payment by stock in liquidation - col: Desc_ op: == value: Capital repayment how: any mode: simple col: Desc_ value: Capital repayment - id: step-mhj9gmd5r1uvkye type: core_mapping name: Vendor Mapping config: primary_identifier: Security_RIC ca_type_col: Vendor_MV_ActionType common_mappings: vendor_Event_ID: mode: source params: col: QAI_ID action_specific: REGULAR CASH DIVIDEND: fields: vendor_Action_Type: mode: source params: col: Desc_ vendor_Announcment_Date: mode: date params: source: AnnouncedDate fmt_in: '%Y-%m-%d' vendor_ExDate: mode: date params: source: EffectiveDate fmt_in: '%Y-%m-%d' vendor_CA_Additional_Info: mode: source params: {} vendor_Dividend_Pay_Date: mode: date params: source: PayDate fmt_in: '%Y-%m-%d' vendor_Dividend_Record_Date: mode: date params: source: RecordDate fmt_in: '%Y-%m-%d' vendor_Gross_Dividend_Amt: mode: source params: col: GrossDivRate vendor_Dividend_Currency: mode: source params: col: ISOCurrCode vendor_Net_Dividend_Amt: mode: source params: col: NetDivRate vendor_Dividend_Source_Of_Funds: mode: source params: {} vendor_Dividend_Franking_Amt: mode: source params: {} vendor_Announcement_Date: mode: date params: source: AnnouncedDate fmt_in: '%Y-%m-%d' SPECIAL CASH DIVIDEND: fields: vendor_Action_Type: mode: source params: col: Desc_ vendor_Announcment_Date: mode: date params: source: AnnouncedDate fmt_in: '%Y-%m-%d' vendor_ExDate: mode: date params: source: EffectiveDate fmt_in: '%Y-%m-%d' vendor_CA_Additional_Info: mode: source params: col: '' vendor_Dividend_Pay_Date: mode: date params: source: PayDate fmt_in: '%Y-%m-%d' vendor_Dividend_Record_Date: mode: date params: source: RecordDate fmt_in: '%Y-%m-%d' vendor_Gross_Dividend_Amt: mode: source params: col: GrossDivRate vendor_Dividend_Currency: mode: source params: col: ISOCurrCode vendor_Net_Dividend_Amt: mode: source params: col: NetDivRate vendor_Dividend_Source_Of_Funds: mode: source params: {} vendor_Dividend_Franking_Amt: mode: source params: {} vendor_Announcement_Date: mode: date params: source: AnnouncedDate fmt_in: '%Y-%m-%d' - id: step-mhj9gmd58u6jlxp type: core_finish name: Finish Line config: {} vendor_key: QAI identifier: *id001 preprocess: - step: compute name: Create QAI ID outputs: *id002 - step: dedupe name: Get rid of duplicates subset: QAI_ID keep: first - step: compute name: Handle Gross & Net Dividends outputs: *id003 - step: classify_events target_col: Vendor_MV_ActionType rules: *id004 mapping: primary_identifier: Security_RIC ca_type_col: Vendor_MV_ActionType common_mappings: vendor_Event_ID: kind: col name: QAI_ID action_specific: REGULAR CASH DIVIDEND: fields: vendor_Action_Type: kind: col name: Desc_ vendor_Announcment_Date: kind: date source: AnnouncedDate vendor_ExDate: kind: date source: EffectiveDate vendor_CA_Additional_Info: kind: col name: null vendor_Dividend_Pay_Date: kind: date source: PayDate vendor_Dividend_Record_Date: kind: date source: RecordDate vendor_Gross_Dividend_Amt: kind: col name: GrossDivRate vendor_Dividend_Currency: kind: col name: ISOCurrCode vendor_Net_Dividend_Amt: kind: col name: NetDivRate vendor_Dividend_Source_Of_Funds: kind: col name: null vendor_Dividend_Franking_Amt: kind: col name: null vendor_Announcement_Date: kind: date source: AnnouncedDate SPECIAL CASH DIVIDEND: fields: vendor_Action_Type: kind: col name: Desc_ vendor_Announcment_Date: kind: date source: AnnouncedDate vendor_ExDate: kind: date source: EffectiveDate vendor_CA_Additional_Info: kind: col name: '' vendor_Dividend_Pay_Date: kind: date source: PayDate vendor_Dividend_Record_Date: kind: date source: RecordDate vendor_Gross_Dividend_Amt: kind: col name: GrossDivRate vendor_Dividend_Currency: kind: col name: ISOCurrCode vendor_Net_Dividend_Amt: kind: col name: NetDivRate vendor_Dividend_Source_Of_Funds: kind: col name: null vendor_Dividend_Franking_Amt: kind: col name: null vendor_Announcement_Date: kind: date source: AnnouncedDate postprocess: []
