StepstoRunningInterDeskReconciliation
Runtime:30minutesReconciliationtime:60minutes
OpenInterdeskRec.mdb
L:\COMCON\BalanceSheet\InternalTrades,Password:z
Compact&repairthedatabase
Tools–DatabaseUtilities–CompactandRepairDatabase
(IfsomeoneisintheExcelfile,thiswillnotwork)
Refreshallthreeomegaqueriesforyesterday’sdate
Tools–Add-Ins–Omega
Login–Execute
Checkthedatesinthetablestoensurethequeriesranproperly
RuntheFullInterdeskmacro
OpenthelastInterSystemRecExcelFile
L:\COMCON\BalanceSheet\InternalTrades\2013
Gotocurrentmonth’sandopenthemostrecentfile
Savesasnewrundate(T-1)
Refreshallthetabs
ResearchbreaksintheFuturesBreaks&IdeskBreakstabs
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
RefreshbothpivottablesintheSummarytab
ReviewthebreaksintheSummarybySystemtab
Iftherearebreaksgreaterthan$500K,researchtheminthematchedtabs:
UsetheinformationontheSummarybySystemtab(eg:Edison-Franklin,Physical)tofilteroutthetradesintheMatchedtabs.ThentakethetotalPLDifferencecolumnandinsertitintotheBackOutcolumnintheSummarytab.ThisfigureshouldbelargeenoughtobringtheAdjustedPNLcolumndowntolessthan$500K.
Oncethat’sdone,makesurebothCellsL3andL4intheSummarybySystemtabaregreen
Iftheyarered,sometradeswereduplicated:
Ineachmatched&breaktab,copyandpatetheHDRcolumntwiceintoanewsheet
Pivotoffthedata,andrightclicktoshowtheCountinFieldSettings—duplicatedtradeswouldhaveacountof>1
Witheachduplicatedtrade,findinitscorrespondingtabthePNL(ColJ)&theCount(ColK)andintheNotestab,inserttheinformation.ThiswilladjusttheduplicatesintheSummarybySystemtab.
|
|
