-- from order to sec_id
select * from ts_order where order_id=1700007815
select 'Parent', SEC_ID, SEC_NAME,* from csm_security where sec_id=1700007812
-- from parent sec_id to leg sec_id
select 'Legs',SEC_ID, SEC_NAME,* from csm_security where parent_sec_id = 1700007812
-- from sec_id to underlying
select 'uPL1L2',* from CRDQT..CSM_UNDERLYING_SECURITY where sec_id in (1700007812,1700007813,1700007814)
select 'up', * from CSM_SECURITY where sec_id=null
select 'u1l', * from CSM_SECURITY where sec_id=null
select 'u2l', * from CSM_SECURITY where sec_id=null
-- from sec_id to orig-sec_id
select 'cust',ORIG_SEC_ID,SEC_ID,* from CSM_SECURITY_CUST where sec_id in (1700007812,1700007813,1700007814)
select 'custP',sec_id, sec_name,* from csm_security where sec_id=null
select 'custLeg1',sec_id, sec_name,sec_typ_Cd, * from csm_security where sec_id=null
select 'custLeg2' sec_id, sec_name,sec_typ_Cd,* from csm_security where sec_id=1700002237
-- list order udf
select * from CRDQT..ts_order_sec_spec where order_id=1700007815
Wednesday, July 29, 2009
Saturday, May 9, 2009
Excel Converter 2003 XML to OpenXML
Vendors like RIMM Black Berry can render OpenXML correctly in its native. The following are code to read Excel 2003 XML spread sheet and construct OpenXML:
using System;
using System.Collections.Generic;
using System.Text;
using ICSharpCode.SharpZipLib.Zip;
using System.IO;
using System.Xml;
using System.Data;
using System.Collections;
using System.Xml.XPath;
namespace JQD.OpenXMLConverter
{
public class ExcelConverter
{
#region Properties and Fields
string _FilePathExcelXML;
string _FilePathExcelOpenXMLTemplate;
string _FilePathExcelOpenXML;
string _DirectoryOpenXMLZipFiles;
Dictionary<string, int> _SharedStrings = new Dictionary<string, int>();
int _NonUniqueStringsCount = 0;
public string FilePathExcelXML
{
get { return _FilePathExcelXML; }
set { _FilePathExcelXML = value; }
}
public string FilePathExcelOpenXMLTemplate
{
get { return _FilePathExcelOpenXMLTemplate; }
set { _FilePathExcelOpenXMLTemplate = value; }
}
public string FilePathExcelOpenXML
{
get { return _FilePathExcelOpenXML; }
set { _FilePathExcelOpenXML = value; }
}
public string DirectoryOpenXMLZipFiles
{
get { return _DirectoryOpenXMLZipFiles; }
set { _DirectoryOpenXMLZipFiles = value; }
}
#endregion
#region Methods
public void ConvertToOpenXml()
{
StringReader strR;
using (StreamReader sr = new StreamReader(FilePathExcelXML))
{
strR = new StringReader(sr.ReadToEnd().Replace("x:", "")); // due to XML exception
}
using (XmlTextReader r = new XmlTextReader(strR))
{
int sheetNumber = 1;
for (r.MoveToContent(); r.Read(); )
{
if (r.Name == "Worksheet" && r.IsStartElement())
{
MapWorksheetToOpenXmlSheet(r.ReadOuterXml(), sheetNumber);
sheetNumber++;
}
}
}
WriteSharedString();
}
private void WriteSharedString()
{
using (XmlTextWriter writer = new XmlTextWriter(DirectoryOpenXMLZipFiles + @"\xl\sharedStrings.xml", Encoding.UTF8))
{
writer.WriteStartDocument(true);
writer.WriteStartElement("sst");
writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
writer.WriteAttributeString("count", _NonUniqueStringsCount.ToString());
writer.WriteAttributeString("uniqueCount", _SharedStrings.Count.ToString());
foreach (string str in _SharedStrings.Keys )
{
writer.WriteStartElement("si");
writer.WriteElementString("t", str);
writer.WriteEndElement();
}
writer.WriteEndElement();
}
}
private void MapWorksheetToOpenXmlSheet(string xmlString,int sheetNumber)
{
List<List<string>> RowOfCellList = new List<List<string>>();
StringReader strR = new StringReader(xmlString);
using (XmlTextReader r = new XmlTextReader(strR))
{
for (r.MoveToContent(); r.Read(); )
{
string s1;
if (r.Name == "Row" ) // very strange has to work this way.
{
while ((s1 = r.ReadOuterXml()).StartsWith("<Row"))
{
RowOfCellList.Add(GenerateCellList(s1));
}
}
}
}
AddToSharedString(RowOfCellList);
WriteWorksheet(DirectoryOpenXMLZipFiles + @"\xl\worksheets\sheet"+sheetNumber.ToString()+".xml", RowOfCellList);
}
private void AddToSharedString(List<List<string>> RowOfCellList)
{
for (int row = 0; row < RowOfCellList.Count; row++)
{
for (int column = 0; column < RowOfCellList[row].Count; column++)
{
_NonUniqueStringsCount++;
if (!_SharedStrings.ContainsKey(RowOfCellList[row][column]))
_SharedStrings.Add(RowOfCellList[row][column], _SharedStrings.Count); // 0 based
}
}
}
private ListGenerateCellList(string cellString)
{
List<string> CellList = new List<string>();
StringReader strR = new StringReader(cellString);
using (XmlTextReader r = new XmlTextReader(strR))
{
int prevCellIndex = 0;
for (r.MoveToContent(); r.Read(); )
{
if (r.Name == "Cell" && r.IsStartElement())
{
int currCellIndex=Convert.ToInt32( r.GetAttribute("ss:Index"));
for (int i = 0; i < currCellIndex - prevCellIndex - 1; i++) CellList.Add(string.Empty); // could have skipped empty cell by missing a cell element
prevCellIndex=currCellIndex;
CellList.Add(ExtractData(r.ReadOuterXml()));
}
}
}
return CellList;
}
private string ExtractData(string xmlData)
{
// "<Cell ss:Index=\"1\" ss:StyleID=\"BoldFace\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\">
// <Data ss:Type=\"String\">Break</Data></Cell>"
StringReader strR = new StringReader(xmlData);
string ret;
using (XmlTextReader r = new XmlTextReader(strR))
{
ret = r.ReadElementString();
}
strR.Close();
return ret;
}
public void WriteWorksheet(string sheetXmlPath, List<List<string>> RowCellList)
{
using (XmlTextWriter writer = new XmlTextWriter(sheetXmlPath, Encoding.UTF8))
{
int colCountMax = 0;
foreach (List<string> cL in RowCellList) if (cL.Count > colCountMax) colCountMax = cL.Count;
writer.WriteStartDocument(true);
writer.WriteStartElement("worksheet");
writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
writer.WriteAttributeString("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
writer.WriteStartElement("dimension");
string lastCell;
if (colCountMax == 0) lastCell = RowColumnToPosition(0, 0); //A1;
else
lastCell = RowColumnToPosition(RowCellList.Count - 1, colCountMax-1);
writer.WriteAttributeString("ref", "A1:" + lastCell);
writer.WriteEndElement();
writer.WriteStartElement("sheetViews");
writer.WriteStartElement("sheetView");
writer.WriteAttributeString("tabSelected", "1");
writer.WriteAttributeString("workbookViewId", "0");
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteStartElement("sheetFormatPr");
writer.WriteAttributeString("defaultRowHeight", "15");
writer.WriteEndElement();
//writer.WriteStartElement("cols");
//for (int i = 0; i < colCountMax; i++)
//{
// writer.WriteStartElement("col");
// //writer.WriteAttributeString("min", "11");
// //writer.WriteAttributeString("max", "20");
// //writer.WriteAttributeString("width", "11.20");
// //writer.WriteAttributeString("bestFit", "1");
// //writer.WriteAttributeString("customWidth", "1");
// writer.WriteEndElement();
//}
//writer.WriteEndElement();
writer.WriteStartElement("sheetData");
WritesheetData(writer, RowCellList);
writer.WriteEndElement();
writer.WriteStartElement("pageMargins");
writer.WriteAttributeString("left", "0.7");
writer.WriteAttributeString("right", "0.7");
writer.WriteAttributeString("top", "0.75");
writer.WriteAttributeString("bottom", "0.75");
writer.WriteAttributeString("header", "0.3");
writer.WriteAttributeString("footer", "0.3");
writer.WriteEndElement();
writer.WriteEndElement();
}
}
public void WritesheetData(XmlTextWriter writer, List<List<string>> RowOfCellList)
{
for (int row = 0; row < RowOfCellList.Count; row++)
{
writer.WriteStartElement("row");
writer.WriteAttributeString("r", (row+1).ToString());
writer.WriteAttributeString("spans", "1:" + RowOfCellList[row].Count.ToString());
for (int column = 0; column < RowOfCellList[row].Count ; column++)
{
writer.WriteStartElement("c");
writer.WriteAttributeString("r", RowColumnToPosition(row, column));
string key,val;
writer.WriteAttributeString("t", "s");
key = RowOfCellList[row][column];
val = _SharedStrings[key].ToString();
writer.WriteElementString("v", val.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
}
}
public void CreateBaseOpenXMLFilesFromTemplate()
{
// clean up
DirectoryInfo di = new DirectoryInfo(_DirectoryOpenXMLZipFiles);
foreach (FileInfo fi in di.GetFiles()) fi.Delete();
foreach (DirectoryInfo sdi in di.GetDirectories()) sdi.Delete(true);
// extract Excel Template into the directory
FastZip fz = new FastZip();
fz.ExtractZip(_FilePathExcelOpenXMLTemplate,_DirectoryOpenXMLZipFiles,null);
}
public void CreateOpenXMLExcelFile()
{
FastZip fz = new FastZip();
fz.CreateZip(_FilePathExcelOpenXML, _DirectoryOpenXMLZipFiles, true, null);
}
#endregion
#region Helper methods
string ColumnIndexToName(int columnIndex)
{
char second = (char)(((int)'A') + columnIndex % 26);
columnIndex /= 26;
if (columnIndex == 0)
return second.ToString();
else
return ((char)(((int)'A') - 1 + columnIndex)).ToString() + second.ToString();
}
string RowIndexToName(int rowIndex)
{
return (rowIndex + 1).ToString();
}
string RowColumnToPosition(int row, int column)
{
return ColumnIndexToName(column) + RowIndexToName(row);
}
#endregion
}
}
Sunday, April 5, 2009
Digital Call Option value = exp(-rt)N(d2)
Black Scholes says C= SN(d1) - Xexp(-rt)N(d2) = exp(-rt){SN(d1)exp(rt)-XN(d2)},
So for risk-neutral, N(d2)= Pr(Price end up above Strike).
Digital Call = Pay M if expired above X. So Digital Call Value =exp(-rt)MN(d2).
In general with Yield,
C=exp(Y-r)tMN(d2)
P=exp(Y-r)tMN(-d2)
Forward Start Call Option = Straight Call Option
This only be true when all of the following are true:
(1) Forward Start is designed to be at-money-call.
(2) Interest Rate and Volatility are constant.
Using Black-Scholes Model, give Forward Start time periods [t1,T1] and calls
are at the money at t=0 and t=t1, we would have call value c, c1 both are proportional to stock price S, S1. Therefore c1= c(S1/S).
Under risk-nerual, S1= Random variable but E[S1]*exp(-rt1]=S or E[S1]=exp(rt1]S
==> value of forward start call at t=0 = exp(-rt1)E[c1]=exp(-rt1)cE[S1]/S=c
That is V-ForwardStart Call(t=0) = C expiring in T1-t1 days.
To really make interest rate and Volality constant, we will need to enter IR swap and Volatility Swap (Pay floating, receiv fixed)
Friday, March 13, 2009
How to read Crash Dump file (.mdmp) using VS.net 2008
(1) Locate Dump file under doucments and settings\user\localsetting\Application Data\PC health\
(2) open .mdump using VS 2008 and run to break exception.
(3) load SOS.dll by ".load sos" command in the immediate windows.
(4) All the sos command now avaible !help list all command
(5) eg. !pe --- Print Exception and stack trace, !DumpStack, !CLRStack, !EEStack
Also you may need to load symbol from Microsoft Symbol Server, Tool->Options-> Debugging->Symbols --> uncheck "Search ...Manually"
Monday, March 2, 2009
DPAPI Encrypted Password in Database
XIP SDK need password to login. So to use DPAPI encryption we need the scheme with the following properties:
(1) Have to allow encryption per machine
(2) easy to change password once in database to trigger change accross all machines
PASSWORD Schema
CREATE TABLE [dbo].JQD_PASSWORD(
[USER_ID] [varchar](50) NOT NULL,
[PASSWORD] [varchar](1000) NOT NULL,
[NEW_PASSWORD] [varchar](500) NOT NULL,
[INSTALL_MACHINE] [varchar](50) NOT NULL,
[PASSWORD_HASH] [varchar](100) NOT NULL,
CONSTRAINT [PK_JQD_PASSWORD] PRIMARY KEY CLUSTERED
(
[USER_ID] ASC,
[INSTALL_MACHINE] ASC
)
DPAPI Decrypt and use Old-New Password Chain
using (JQD.XIPCommon.Entity.ImportExportXIP.ImportExportXIPDataContext ctx = new JQD.XIPCommon.Entity.ImportExportXIP.ImportExportXIPDataContext(_csImportExportXIPIntSec))
{
var q = from r in ctx.JQD_PASSWORDs
where r.INSTALL_MACHINE == Environment.MachineName &&
r.USER_ID == _TraderName
select new { r.PASSWORD, r.NEW_PASSWORD };
string nPWD=q.First().NEW_PASSWORD;
string oPWD=Encoding.Unicode.GetString(ProtectedData.Unprotect(Convert.FromBase64String(q.First().PASSWORD ), null, DataProtectionScope.LocalMachine));
if (nPWD == string.Empty)
{
LoggingHelper.DoMsmqLogging("Trader Password retrieved successfully", "JQDAllocationService::GetTraderPassword", "Normal", "Info");
return oPWD;
}
else
{
LoggingHelper.DoMsmqLogging("Password change detected, updating password...", "JQDAllocationService::GetTraderPassword", "Normal", "Info");
nPWD=RijndaelSimple.Decrypt(nPWD, oPWD, oPWD, "SHA1", 1, "@1B2c3D4e5F6g7H8", 256);
JQD.XIPCommon.Entity.ImportExportXIP.JQD_PASSWORD pwd = new JQD.XIPCommon.Entity.ImportExportXIP.JQD_PASSWORD();
pwd.USER_ID = _TraderName;
pwd.PASSWORD =Convert.ToBase64String(ProtectedData.Protect(Encoding.Unicode.GetBytes(nPWD), null, DataProtectionScope.LocalMachine));
pwd.INSTALL_MACHINE = Environment.MachineName ;
pwd.NEW_PASSWORD = "";
HashAlgorithm hash = new SHA1Managed();
pwd.PASSWORD_HASH = Convert.ToBase64String((hash.ComputeHash(Encoding.UTF8.GetBytes(pwd.PASSWORD))));
var q2 = (from a in ctx.JQD_PASSWORDs
where a.USER_ID == _TraderName &&
a.INSTALL_MACHINE == Environment.MachineName
select a);
ctx.JQD_PASSWORDs.DeleteAllOnSubmit(q2);
ctx.SubmitChanges();
ctx.JQD_PASSWORDs.InsertOnSubmit(pwd);
ctx.SubmitChanges();
LoggingHelper.DoMsmqLogging("Password update completed. will try login", "JQDAllocationService::GetTraderPassword", "Normal", "INfo");
return nPWD;
}
}
DPAPI encrypt post installation
string DPAPIEncrypt(string clearText)
{
return Convert.ToBase64String(ProtectedData.Protect(Encoding.Unicode.GetBytes(clearText ), null, DataProtectionScope.LocalMachine));
}
private void SavePassword()
{
string MachineName = Environment.MachineName;
string cs = ConfigurationManager.ConnectionStrings["PasswordManagement.Properties.Settings.ImportExportXIPConnectionString"].ConnectionString;
using (ImportExportXIPDataContext ctx = new ImportExportXIPDataContext(cs)) // use integrated security
{
JQD_PASSWORD pwd = new JQD_PASSWORD();
pwd.USER_ID = tbUserID_Installation.Text;
pwd.PASSWORD = DPAPIEncrypt(tbPassword_Installation.Text);
pwd.INSTALL_MACHINE = MachineName;
pwd.NEW_PASSWORD = "";
HashAlgorithm hash = new SHA1Managed();
pwd.PASSWORD_HASH = Convert.ToBase64String((hash.ComputeHash(Encoding.UTF8.GetBytes(tbPassword_Installation.Text))));
var q = (from a in ctx.JQD_PASSWORDs
where a.USER_ID == tbUserID_Installation.Text &&
a.INSTALL_MACHINE == MachineName
select a);
ctx.JQD_PASSWORDs.DeleteAllOnSubmit(q);
ctx.SubmitChanges();
ctx.JQD_PASSWORDs.InsertOnSubmit(pwd);
ctx.SubmitChanges();
}
}
Change Password
private void btnChange_Click(object sender, EventArgs e)
{
string MachineName = Environment.MachineName;
string cs = ConfigurationManager.ConnectionStrings["PasswordManagement.Properties.Settings.ImportExportXIPConnectionString"].ConnectionString;
HashAlgorithm hash = new SHA1Managed();
string entered_pwd_hash = Convert.ToBase64String((hash.ComputeHash(Encoding.UTF8.GetBytes(tbOldPassword.Text))));
using (ImportExportXIPDataContext ctx = new ImportExportXIPDataContext(cs)) // use integrated security
{
var q = (from a in ctx.JQD_PASSWORDs
where a.USER_ID == cbUserID.SelectedValue.ToString() &&
a.PASSWORD_HASH==entered_pwd_hash
select a);
if (q.Count()==0)
{
MessageBox.Show("No machines use the old password entered. You may try to enter another old password");
WriteToLog("No machines use the old password entered, Therefore no change of password");
return;
}
if (!TestPassword(cbUserID.SelectedValue.ToString(), tbNewPassword.Text))
{
WriteToLog("New password is invalid. No change will be made.");
return;
}
foreach (JQD_PASSWORD pwd in q)
{
pwd.NEW_PASSWORD = RijndaelSimple.Encrypt(tbNewPassword.Text, tbOldPassword.Text, tbOldPassword.Text, "SHA1", 1, "@1B2c3D4e5F6g7H8", 256);
WriteToLog("New password saved for "+pwd.USER_ID+", "+pwd.INSTALL_MACHINE+" and will be picked up by installed services" );
}
ctx.SubmitChanges();
}
}
Rijndael Symmetric Encryption
public class RijndaelSimple
{
public static string Encrypt(string plainText,
string passPhrase,
string saltValue,
string hashAlgorithm,
int passwordIterations,
string initVector,
int keySize)
{
byte[] initVectorBytes = Encoding.ASCII.GetBytes(initVector);
byte[] saltValueBytes = Encoding.ASCII.GetBytes(saltValue);
byte[] plainTextBytes = Encoding.UTF8.GetBytes(plainText);
PasswordDeriveBytes password = new PasswordDeriveBytes(
passPhrase,
saltValueBytes,
hashAlgorithm,
passwordIterations);
byte[] keyBytes = password.GetBytes(keySize / 8);
RijndaelManaged symmetricKey = new RijndaelManaged();
symmetricKey.Mode = CipherMode.CBC;
ICryptoTransform encryptor = symmetricKey.CreateEncryptor(
keyBytes,
initVectorBytes);
MemoryStream memoryStream = new MemoryStream();
CryptoStream cryptoStream = new CryptoStream(memoryStream,
encryptor,
CryptoStreamMode.Write);
cryptoStream.Write(plainTextBytes, 0, plainTextBytes.Length);
cryptoStream.FlushFinalBlock();
byte[] cipherTextBytes = memoryStream.ToArray();
memoryStream.Close();
cryptoStream.Close();
string cipherText = Convert.ToBase64String(cipherTextBytes);
return cipherText;
}
public static string Decrypt(string cipherText,
string passPhrase,
string saltValue,
string hashAlgorithm,
int passwordIterations,
string initVector,
int keySize)
{
byte[] initVectorBytes = Encoding.ASCII.GetBytes(initVector);
byte[] saltValueBytes = Encoding.ASCII.GetBytes(saltValue);
byte[] cipherTextBytes = Convert.FromBase64String(cipherText);
PasswordDeriveBytes password = new PasswordDeriveBytes(
passPhrase,
saltValueBytes,
hashAlgorithm,
passwordIterations);
byte[] keyBytes = password.GetBytes(keySize / 8);
RijndaelManaged symmetricKey = new RijndaelManaged();
symmetricKey.Mode = CipherMode.CBC;
ICryptoTransform decryptor = symmetricKey.CreateDecryptor(
keyBytes,
initVectorBytes);
MemoryStream memoryStream = new MemoryStream(cipherTextBytes);
CryptoStream cryptoStream = new CryptoStream(memoryStream,
decryptor,
CryptoStreamMode.Read);
byte[] plainTextBytes = new byte[cipherTextBytes.Length];
int decryptedByteCount = cryptoStream.Read(plainTextBytes,
0,
plainTextBytes.Length);
memoryStream.Close();
cryptoStream.Close();
string plainText = Encoding.UTF8.GetString(plainTextBytes,
0,
decryptedByteCount);
return plainText;
}
}
Sunday, February 15, 2009
LINQ Not In Query (Process[]).Contains does not work
Process[] psBeforeLogin = Process.GetProcessesByName("MxOrderGenerator");
MXXOMLib.MxEnumReturnCode ret = _MxgXOM.Login(TraderName, TraderPassword, XIPDBServer, "GMO_INTRADAY", MXXOMLib.MxEnumEncryptionMethod.MxeEncryptionMethodNone);
Process[] psAfterLogin = Process.GetProcessesByName("MxOrderGenerator");
var p = from rA in psAfterLogin
where !(from rB in psBeforeLogin select rB).Contains(rA)
select rA;
Console.WriteLine(p.Count());
count =2 always, it should be one.
As it turns out, cannot use Process in select. Can use ProcessID.
var p = from rA in psAfterLogin
where !(from rB in psBeforeLogin select rB.Id).Contains(rA.Id)
select rA;
Subscribe to:
Posts (Atom)