StepstoRunningIntraDeskReconciliation
Runtime:90minutesReconciliationtime:60minutes
BeforeanyInternalsReconciliationsareran,
OpenbothNorthAmerica&EuropeEdisonRecFiles
L:\COMCON\BalanceSheet\InternalTrades\EdisonRec\EdisonInternalTradeReport(Europe)&(NA)
Refreshall
OpenIntraDeskRec.mdb
L:\COMCON\BalanceSheet\InternalTrades,Password:z
Compact&repairthedatabase
Tools–DatabaseUtilities–CompactandRepairDatabase
(IfsomeoneisintheExcelfile,thiswillnotwork)
Refreshallthreeomegaqueriesforyesterday’sdate
Tools–Add-Ins–Omega
Login–Execute
Checkthedatesinthetablestoensurethequeriesranproperly
RuntheIntraInternalsRecmacro
OpenthelastIntraSystemRecExcelFile
L:\COMCON\BalanceSheet\InternalTrades\2013
Gotocurrentmonth’sandopenthemostrecentfile
Savesasnewrundate(T-1)
Refreshallthetabs
ResearchallthedifferencesintheSummarytabthatarebothsameregion&over$500K
ThesesubstantiationscanbefoundeitherintheFundingtab(forfundingtrades)andMatchedtab(forallothertradetypes)
Forstrategies1670&1663,thesefundingbreaksareusuallyduetoglobalvs.regionaltiming
EnsurethatcellL2(thecheck)isgreen
Ifit’sred,thereareduplicatedtrades
Startingwiththematchedtabs,selectalltheheadersincolumnBandpastethemintothePossibleDuplicatestableinInternalResearchdatabase(L:\COMCON\BalanceSheet\InternalTrades)
Copyandpastetheiroffsettingtrades(ColumnN)intothetableaswell
RuntheSearchingforDuplicatesquerytogetalltheheadersthatareduplicateintothefeed
Repeattheprocessforalltabsuntilyoufindalltheduplicates
Thecountwillbe>1
Oncetheduplicateshavebeenfound,findtheheadersintheExcelfileandtheduplicatedP&L
Adjustfortheseduplicatescells
IntheNotestab,inserttheheadersandtheirP&Lunderthecorrespondingrow
Thetotaladjustment(CellQ21)shouldbereflectedinthecheck(CellL2)intheSummarytab
Keepadjustinguntilthecheck(cellL2)is0and/orbacktogreen
ResearchtheTFDBreaksTab:
ClearallnotesintheNotescolumn
FiltertheTradeDatecolumnfortherundate—thesebreaksareTimingBreaks(OffsetmissedP&Lcommit)
Tosubstantiatetheremainingofthetrades,youmustfindtheoffsets:
Copyandpastethetrades’HDRnumbersandoffsetsintotheConcatenatingFile
RuntheOmegaqueryintheInternalResearchMSRfile,andinthe“HeaderNumin”line,pasteinthelastrow’sleftcolumnconcatenation(columnE)
IfPLState=F&ComponentID=3(OffsetFinalized
IfPLState=U&Co
Substantiateasmanybreaksaspossible,andescalatetheonesyoucan’t
ResearchtheYEBreaksTab
Clearpre-existingnotesintheNotescolumn
FiltertheTradeDatecolumnfortherundate—thesebreaksareTimingBreaks(OffsetmissedP&Lcommit)
Tosubstantiatetheremainingofthetrades,youmustfindtheoffsets:
Usingthetrades’HDRnumbers,useExcel’svlookupfunctiontofindtheoffsetsinpriordate’sIdeskmatchedtabs
CopyandpastebothHDRsandOffsetsintotheConcatenatingFile
RuntheOmegaqueryintheInternalResearchMSRfile,andinthe“HeaderNumin”line,pasteinthelastrow’sleftcolumnconcatenation(columnE)
ForthecombinationsofComponentIDs&PLState,seeattached“Inter/IntraTypesofBreaks’guide
Substantiateasmanybreaksaspossible,andescalatetheonesyoucan’t(group:commodopsglacontrol)
|
|
