FinancialControllersGroup-InternalTrades
FX/ird–DailyProcess
TableofContents
Page
I. DownloadingTradeData/FXRates 3-18
DatafromSTS 3-8
FXRatesfromEQS 9-10
FXTradeData 11-12
FXOptions 13-14
T_TradesDatafromWiseDB 15
II. RunningMatchingProceduresintheDatabase 16-18
III. InvestigationProcessforUnmatchedTrades 19-26
IV. OptionRoll-Up 26-31
V. WeeklyCleanUpProcedure 32
VI. Close-OutTrade 33-35
VII. MonthEndPatching 36-40
VIII. ArchivingFXDatabase 41-48
I.DownloadingTradeData/FXRates
1.DownloadingdatafromSTS(SwapsTradingSystem)
WhenanInterestratetraderexecutesanFXtradewiththeFXDesk,theinterestratetraderwillbookhis/hertradeinSTSwhiletheFXtraderwillbooktheirsideofthetradeinDDI(FXRiskSystem).AsaresultweneedtopulltheinternaltradedatabothfromSTSandDDI.
LogintoSTSControllerSessionsforLN,NY,SY,HK,TK,andSE(Seoul).
DownloadtheFXInternalTradesfromtheAsiaSTSSessions(SY,HK,TK,SE)asthesessionsmaynotbeavailableafter10am
StepstodownloadFXInternalTradesfromSTS:
YouwilldownloadtheinternaltradesfromeachSTSSessionseparately.
IneachrespectiveSTSSessionyouwanttochangethedatetotheP&Ldateyouwanttoperformthereconciliation:
GotoMarketInfo,SelectChangeDateandchangetothepreviousbusinessdate.
Nextyouwilldownloadthetradedatausingthestoredlibraryforeachregion.
GotoInventory,selectFXTrades
Onthenextscreen,selectShowPV(thiswillshowthemarketvalueforthetrades)
Manuallytypetherespectiveregion’slibrarynamefromthelistandhitENTER
Recordswillbereturned,waitfor‘’toappearinBookinputbox.
listofregionsbookscanbefoundunder:J:\ftp\shared\InternalTrades\Intrades_FX_IRD\ListofSTSBooksandBookLibraries.xls.
LondonLibrary:lnfxbooks
NYLibrary:nyfxbooks
Tokyo:tkfxbooks
HongKong:hkfxbooks
Sydney:syfxbooks
Seoul:sefxbooks
HitF11tosavetradedata:
Filename:includeregion,date(previousbusinessday)and“.txt”(i.e.,LN_mmddyyyy.txt)
Selecttxt
ThiswillsavethetradedataintoyourSAMBADrive\stsdata
RepeatsamedownloadforALLREGIONS
Whenyouaredone,youshouldseeallthedownloadsasperbelowscreenshotinyourSAMBADrive/stsdata
Thefilesfromthepreviousdatecanbedeleted
UpdatingtheLibraryinSTS
Inordertoview/editthestoredlibraries,goto“Admin”inthemainscreenandclickonLibraryEditorinthedropdownlist:
ClickonLoad,thensearchforthelibraryname
ThebooksontheleftrepresentallbooksavailablewithintheSTSsession.Ifyouwanttoaddanewbooktoanexistinglibrary,thensimplydouble-clickthedesiredbookontheleftanditwillappearontheright(ifyouhavealreadyloadedalibrary).Click‘Save’tosaveyourupdatestothelibrary.
CopySTSTradefilestoShareDrive:
ThefilesdownloadedfromSTSintoyourSAMBAdriveneedtobecopied/movedfromyourSAMBAtoasharedriveastobeaccessiblefortheaccessdatabase,utilizedtoperformthereconciliation,canreadthetradefilesandrunthenecessaryreports.
MakeacopyofdownloadedtradefileslocatedinyourSAMBADrive/stsdata
PastetradefilesintheJdrive:\\Msad\ROOT\NA\NY\LIB\CONTROLLERS\NYCNTFTP1\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\sts
Archivefilesfromthepreviousday
Movefilesintothe“Archive”folder/2012
2.DownloadingFXRatesfromEQS(EnterpriseQueryServices)
NowweneedtodownloadFXRatesfromEQSforthefollowingregions:NY,LN,TK,HK
Type“EQS”ininternetexplorer
UnderReferenceData,Clickon“FXSpotRates”
Insert“”forcurrencyfromfield
MakesurethestartdateandenddateisthePnLdate(T+1)
ClickSubmitandexportdatatoExcel
Onceyouexportedthedata,savethefileunder:
J:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\fx
Filename:NY.xlsorLN.xls(dependingonwhichregionyoudownloadedtheratesfor).
Theexistingfilesforpreviousdownloadscanbeoverwritten/replaced.
Repeatstepsabovefortheremainingregions(LN,TK,HK,NY)
3.FXTradeData
YouhavenowobtainedalltheFXTradesbookedinSTS,nowyouwillneedtodownloadalltheFXtradesbookedinDDI(DirectDealerInput).
GototheFX_IRDDatabase(youshouldalreadyhaveitopen)
JDrive:\\msad\root\NA\NY\LIB\CONTROLLERS\NYCNTFTP1\ftp\shared\InternalTrades\Intrades_FX_IRD\src\intrade_fx_ird_cmltv.mdb
WithinthemainmenuinthedatabasechangedatetoT+1,click‘SetDates’ontheMainScreen(Form)
LogintoOMEGAapplicationandminimize.
Inthedatabase,runird_day_trades.msrviaAdd-ins
TheMSR(MorganStanleyReport)willprovidealistofalltheinternalFXtradesbookedinDDI.
OnthebelowscreenhighlightthereportandclickExecute(youmaybepromptedtologinifyouforgottoopenOMEGA)
Makesurethatthepathwiththescreenbelowstates:Jdrive\internaltrades\intrades_FX_IRD\src
YouhavetoexecutebothMSRreportst_fxandt_fx_II
Leavecounterpartyconstraintasisfort_fxbutfort_fx_IIobtainthelistofaccountfromthisfile(MSRIItab):\\msad\root\NA\NY\LIB\CONTROLLERS\NYCNTFTP1\ftp\shared\InternalTrades\Intrades_FX_IRD\ListofCounterparties.xls
Highlightall3reportnamesandchecktheT+1dateinthetimeframeswindowasshown.ClickUpdate,thenOK
OnthenextscreenclickOK.ThiswillruntheMSR(MorganStanleyReport).CancloseFORMafterreportisfinishedrunning.
NOTE:thereportisdesignedtobringintheFXtradesforthecustomeraccounts(counterpartyaccounts)listedinthe“counterpartyaccountin”constraintfield.Newaccountsmaybeaddedtothereportasneeded.AddaccountonlytothesecondMSR.
4.OptionsData
NeedtopullinOptionstradedataintothedatabase.TheOptionstradedataisautomaticallyexporteddailyfromtheMainframe/SWUB(subledger)toanexcelfileonthenetwork.ThisautomaticfileissaveddailyasCUSTOPTALL-RDX.XLS
Theexcelfilemustbeconvertedintoa.txtfileinorderfortheFX-IRDdatabasetoaccesstheoptiontradesdata.
LocatingtheCUSTOPTALL-RDX.XLS
FilePath:
HighlighttheCUSTOPTALL-RDX.XLSandCOPYthefile
Copythefileto:
RenametheexistingCUSTOPTALL-RDX.txtfilefromthepreviousbusinessdayw/thedatestamp(ofyesterday)
Archiveyesterday’sfile(thefileyoujustrenamed)into“archivefiles”folder\2012
WanttochangetheformatoftheCUSTOPTfilefromxlstotxt.
ThiscanbedonebyrenamingthefileasCUSTOPTALL-RDX.txt
ClickYES
Youcannowclosethenetworkpath
5.T_TradeDatafromWiseDB
Weneedtoobtaint_tradesdatafromSwapsWiseDBbeforerunningthematchingprocedures.Usuallydataisavailableforextractafter10ameverymorning.
OpenaswapsdatabasefromOmegascreen(InternalTrades)
Furtherclickon“MultipleCriteriaReport”
PicktheappropriateData(PnLDate)andenter9forProblemCode
Filepathshouldread“J:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\t_trades\t_trades.xls”
Finallyyetimportantly,clickon“Search”Buttonintopleftcorner.
Oncet_trades.xlsissavedouttothefolder.Archivetheoldfileandrenamet_trades.xlstot_trades.txt
II.RUNNINGMATCHINGPROCEDURES
INTHEDATABASE
AfterallthedatahasbeendownloadedfromSTS,OMEGAandCUSTOPTNYNA,weneedtouploadthisdataintotheFX-IRDDatabase.ThedataisuploadedbyrunningvariousquerieswithintheFX_IRDdatabase(4BOLDEDLinks).
Withinthemenumaintogo:
FXDataPostImportProcedure
ClickonCombineMSRIIintoMSRI
ClickonFXDataPostImport
FXRatesImportProcedure–uploadstheFXRatesdownloadedfromEQS
ClickonFXRatesImportProcedure
STSimportprocedure
ThisprocedurepullsintheSTSbooksthatyoudownloaded.NOTE:Whenyourunthisprocedure,thedatabaseislookingfortheSTStxtfilesinthe“dat”folderintheJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat
FormatOptionsRDXFile–inorderforDBtoproperlyreadthefile
ClickonFormatOptions-NODEFile
ClickonOptionsImportProcedures
IMPORTANT:IflateronyourealizeyouhavemisseddownloadingabookfromSTS,thenyoushouldgoaheadanddownloadthemissingbook,addittothedatfolder(alongwithalltheotherbooks!)andthenre-runSTSimportprocedureandthentheTradeLinkingprocedure.
TradeLinkingProcedure
ThisprocedureMATCHEStheFXtradeswiththeIRDtradesbasedonmatchingordernumbersandvarioustradefactcriteria.Thisshouldmatchamajorityofthetradepopulation.
Thiswilltakeafewminutestorun
III.INVESTIGATIONPROCESSOF
UNMATCHEDTRADES
BeforeinvestigatingFXunmatchedtradeswewouldneedtocopythedatafromMicrosoftAccessintoMicrosoftExcel.
HowtosetuptheFXUnmatchedbeforeinvestigating
Oncethetradelinkingprocedureisdone
Checktoseeunmatchedtradesbyclickingon:
“FXOnlyNOTIRD”–willgiveyoualistofallFXUnmatchedtrades
“IRDOnlyNotFX”–willgiveyoualistofallIRDUnmatchedTrades
“IRDdatatable”–willgiveyoualistofallIRDTradesimportedfromSTS
Openthedailyunmatchedtemplate(themostrecentfile)locatedinthe
\\msad\root\NA\NY\LIB\CONTROLLERS\NYCNTFTP1\ftp\shared\InternalTrades\Intrades_FX_IRD\src\DailyFX_IRDRecs\yyyy\yyyy-mm
Filesaveas,themostrecentP&Ldate(i.e.FXvs.IRD–PnLasof03-06-2012)
Gobacktothedatabaseandcopythetradedataobtainedbyrunningstepnumber5.
ClickOn:
IRDOnlyNotFX
FXOnlyNotIRD
IRDDataTable
PasteIRDOnlyNotFXontotheIRDTab
PasteFXOnlyNotIRDdataontotheFXTab
PasteIRDDataTableontotheIRD-TradesTab
Beforeresearchinganytrades,weneedtolayoutthedataaccordingly.
IRDTab
SortBy
Comments
OrderNumber
FXTab
SoryBy
Matched
Comments
OrderNumber
InvestigatingTradeDetails
WeneedtounderstandthatbothIRDsideshouldhavethesametradedetails.IfthetradedetailsdonotmatchthentheFXDatabasewillnotbeabletorecognizethepairandmatchedthetradesautomatically.Thetradedetailsthatareincorrectmostofthetimesare:
Notional
OrderNumber
ValueDate
Currency
Notional
Example:LHKQFR
ChecktheEURCurrency,youcanseeasperscreenshotthenotionalis451,161.74
WealsoneedtochecktheIRDSidebyusingSTSFXTool,
ToaccesstheSTSFXTool
GotoSTSTraderSession
ClickonFXTrades
EntertheBookoftheordernumber
YoucancheckthebookoftheordernumberbygoingintoSWQB.
GotoSWQB,
PressE(FXHedgeAllocation)
Press2forEnterOptionsandPutintheOrderNumber
LMDSPistheSWAPthatiscorrespondingtotheFXCashOrderNumber
LookuptheSWAPIDthatyouhaveobtainedfromtheFXHedgeAllocation
NowyouknowthebookfortheordernumberLHKQFR.
TypeinthebookintotheSTSFXInventoryTool
LookfortheOrderNumberLHKQFR
DoubleclickontheFXtradeandyoucanseethetradedetails,
MakesurefornotionalthatIRDSideandFXSideshowsdifferentposition.Onesideneedstobeshortandtheothersideneedstobelongposition.
OrderNumber/ValueDate/Currency
Sameasabove,makesureIRDandFXSidebothhavethesameOrderNumber.Iftheordernumberhasn’tbeenupdatedoneithersidethencontacttherespectivegroup,youcanfindthecontactlistinthebelowpath:
J:\ftp\shared\InternalTrades\Intrades_FX_IRD\FXIRDCashContacts.xls
J:\ftp\shared\InternalTrades\Intrades_FX_IRD\FXIRDOptionContacts.xls
FXOptionsRolledUp
FXoptionsneedtobeRolledUptoaSTSreference.InordertofindtheSTSreferencewecanlookinto2places.
LEO
E-MailsfromOperation.
1.Leo
YoucanlookuptheOptionbyclickingon“FxOptionsQuery”
TypeintheFXOptionOrderNumberintheTradeIDNumberfieldandthenclick“Query”
Clickon“WorkItems”
Asyoucanseebelow,theworkitemscommentsstateEK3PPistheSTSref.
2.EmailfromOperation
EverydayinternalsteamshouldreceiveemailsfromOperationwhichshouldhavetheSTSreference.
IftheemailsdonothaveanySTSreferencethenyoucanreplyalltotheTAsandaskfortheSTSreference.
Iftheydonotreply,checkLEOagain,theymighthaveupdatedLEO
HowtoRollUpOptionswithintheDatabase
IfyoudoknowtheSTSreffortheOptionthenwewouldneedtorollupagainsttheSTSrefwithintheDatabase.
TorollupwithintheDB
WithintheReportssection,clickonthedropdown.
Clickon“SwapRoll-UpForm”
Belowisthescreenthatshouldpopup
RightClickon“rollup_swapid”box
ClickonTextFilters\Equals
Enterinthestsref,(exEK3PP)
Enterinthetradedetailsonthestarrow,
Mapping_authorityshouldbethegroupthatconfirmstheSTSref,ifwefoundthereferenceinLEOthenwewouldinput“RiskSystemReference”
Afterfinishinginputtingthetradedetailsyoumayexitoutofthedatabase.
WeeklyCleanUpProcedure
WeneedtoensurethatalltradepopulationwithintheDBmatchesthepopulationinOMEGA.Thisprocesswillcleanupthepopulationwithinbothdatabases.
Wheretofindthesepopulations:
Weneedtofocusontwoqueries,
StartwithCashthenmoveontoOptions
Researchalltradesthatarewithinthisquery.UseSTStoresearchindividualtrades.
RepeatthesamefortheOptionspiece.
FXClose-outTrades
FXcloseouttradeisanequalandoppositetradeinthesamecurrencypair.Forexample,ifyouhavegonelonginonelotofEUR/USDyoucancloseoutthatpositionbysubsequentlygoingshortinoneEUR/USDlot(attheprevailingrate).
?
Typically,wewouldhaveonetradebookedinSTSandtwotradesbookedinFX-DDI(LEO).AnSTStradewillbematchedwithoriginalFX-DDIoneandtheclose-outwillremainunmatched.Beforewecommentitasaclose-out,weneedtoverifyit.Belowisanexampleonhowtocheckaclose-outtradeinSTSandLEO.
ThisFX-DDItradeispopulatingonourFXunmatchedreport.Wewouldlookforclose-outordernumberonthebottom:
?
ThancheckthatreferenceinLEO.Bothscreenshotstellus532mmCLPwasoriginallysold?atarateof532.000andthanbought?atarateof529.230.NowletusseeifthesetradedetailsinadditiontoothersmatchSTS.
?ThelaststepwouldbetocheckthebookinginSTS.SettlementFXRatefieldhas?arate?529.2300000.AllscreenshotstellustheoriginaltradewasEP9ULJandwasclosed-out,refEP9UULwithallthetradedetailsinsync.NowwecancommentEP9UULasaclose-outtradeofEP9ULJontheFXunmatchedreport.
FYI-Thistradehasnotbeenclosedoutyet.SettlementFXRateisblank.
MonthEndProcedure
DuringMonth-End,weneedtoensurewedownloadtheSTSDatafromthemonth-endsessions.ToDownloadtheSTSMonth-EndDataweneedtoopenuptheSTSMonth-EndSessions.
STS_LN_monthend
STS_NY_monthend
STS_TK_monthend
STS_HK_monthend
STS_SE_monthend
STS_SY_monthend
RepeattheprocessStep1onpage3.Downloadthedataformonth-end.
Fortherestofthedatarepeattheusual,makesurethePnLDateisfortheCurrentMonth-EndDate,
Afterfinishingimportingthedatamakesuretoinvestigateallunmatchedtrades.
UsetheAboveFXClose-OutTradestodeterminetheCloseoutTrades
DailyMonthEndPatching
OpenLastMonthEndFile
Copythe1standtheLast4Tabs(MetricsPivots,TradestoPatch,PatchesTable,IRD-AfterPatch,FX-AfterPatch)
FirstGointotheTradestoPatchTab,
Deletethepreviousmonthdata.TheToptableistheFXSideData,copyandpastetheFXdatathatneedstobefixedafteralltheresearch.Makesureyoucopythedataaccordingtotheheadersonthistable.
Forthebelowsection,copytheIRDdata.Sameliketheabove,identifythetradesthatneedtobefixedandcopythedataaccordingtotheheaderonthistable.
Requiredfieldmustbefilled
Moveontothenexttab,PatchesTable,
Thetableshouldhavebeenpopulated,ifnotmanuallypopulatethetable.Makesurethistableiscorrectwithallthefixingthatneedstobedone.Thistableisgoingtobeusedtofixtheordernumber/valuedate/notional/currency/tradedate/PV..Etc
GototheFXDatabase,ClickonIRDRecordPatchesTable
Scrollallthewaytothebottomofthetable
GobacktothePatchesTable,copythedataandmakesureyoucopyfromcolumnAtoS.Ifyoudonotcopyallthecolumns,thepatcheswillnotworkwithintheFXDatabase.
PastethedataintotheIRDRecordPatchesTable.Makesuretopasteatthebottomofthetable;donotreplaceanyoftheoldpatches.Hint:RightClickonthestar()onthebottomofthetableandclickpaste
AfterpastingcloseoutfromtheIRDRecordPatchesTable,RuntheSTSImportProcedureandthenclickonAdd0.01toAllTradesfromSTS.(Click“Yes”onthepopupthatappears)
LinkthetradesagainbyrunningtheTradeLinkageProcedure
AfterthelinkingprocessopenupIRDOnlyNotFXandFXOnlyNotIRD
CopytheIRDOnlyNotFXdataintoIRD-afterpatchtabintheexcelfile.
CopytheFXOnlyNotIRDdataintoFX-afterpatchtabintheexcelfile.
Makesureonbothtabs(IRD-afterpatchandFX-afterpatch)thepatcheswereclearedout.Ifthetradesarestillinthereport,revisitthestepsaboveandcheckforanymistakes.
CopyandPastetheFormulaonbothTabs(IRD-afterpatchandFX-afterpatch)(LMV/SMV,<500K)
Updatethe1stTab,MetricsPivots
ThisconcludestheMonthEndFXDailyProcedure
ArchivingFXDatabase
Wewillneedtoarchivethedatabaseafterproceedingthemonth-endpatching.Thiswillallowotherteammembertoworkonmonth-endmetricswhileweruntheFXdailyforthenextPnLdate.
GototheDailyFXfolder:J:\ftp\shared\InternalTrades\Intrades_FX_IRD
Gointothearc-monthendversionsfolder
0currentmonthwillbethemostrecentmonthend(i.eMarch2012)files
1monthendwillbethemostrecentmonthend+1month(i.eFeb2012)files
Followedby2,3and4month
Beforeimportinganydataintothisfolder,wemustrename4monthendtothecorrespondingmonth(i.e2011-12)afterrenamingcompressthefolderintoazippedfolder.
Aftercompressing,movethefileintotheArchiveMonths
Nowyoucanrenamethefolders:
3monthendto4monthend
2monthendto3monthend
1monthendto2monthend
0currentmonthto1monthend
Createanewfoldercalled0currentmonth
Withinthe0currentmonthfoldercreate2folders
Indatfolder,create4newfolders(fx,options-NODE,sts,t_trades)
GotoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\fx
Copypastethefilesasbelowforfx.DoNOTcopyArchiveFilesandPriorMonth
Pastethe4copiedfiles(NY,HK,LNandTK)intoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\dat\fx
GotoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\options-NODE
Copythefilesasbelowforoptions-NODEDoNOTcopyArchivedFolder
PastetheCUSTOPTALL-RDX.TXTfileintoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\dat\options-NODE
GotoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\sts
CopythefilesasbelowforstsDoNOTcopyArchivedFilesFolder
PastethestsfilesintoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\dat\sts
GotoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\dat\t_trades
Copythefilesasbelowfort_tradesDoNOTcopyArchivedFolder
PastethestsfilesintoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\dat\t_trades
Forsrcfolder
GotoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\src
Copyonlythefollowingfiles
TheFollowingFilesAre:(Allofthesefilesneedstobepastedtosrcfolder)
PastethesefilestoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\src
OpenuptheDatabasethatyoujustcopiedovertoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\src
Makesurethedatabasehasbluescreeninsteadofyellow.
PressF11,thenlistoftablesshouldappearontheleft
RightClickonANYtableandthenclickonLinkedTableManager
LinkedTableManagerPopupwillappear
ClickontheFollowing:
CUSTOPTALL-RDX
Fxhk
Fxln
Fxny
Fxtk
T_trades
Alwayspromptfornewlocation
ClickOKafterclickingalltheabove
ItwillaskforNewLocationofCUSTOPTALL-RDX(Link)
GotoJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\dat\options-NODEandselectthecustoptall-rdx.txtfile
Repeatthisforalltheotherpopup.MakesureyouselectthedatasourcefromtheJ:\ftp\shared\InternalTrades\Intrades_FX_IRD\arc-monthendversions\0currentmonth\dat
4/9/2012 Page2of48
|
|
