public static object GetMarket(string Contract, string TickType)
{
try
{
// Check to make sure it's a valid tick type...
Enum.Parse(typeof(IBTickType), TickType);
Contract cc = XLOM.Get <Contract>(Contract);
object x = "Error, no contract with key '" + Contract + "'!";
if (cc != null)
{
x = XlCall.RTD("XL.TickServer", null, Contract, TickType);
}
return(x);
}
catch (ArgumentException ae)
{
Debug.WriteLine("GetMarket: " + ae.ToString());
return("Error: Couldn't parse tick type. " + ae.ToString());
}
catch (Exception e)
{
Debug.WriteLine("GetMarket: " + e.ToString());
return(e.ToString());
}
}
public static object CreateContract(string Symbol, string Type, string Exchange, string Currency, string LotSizeOpt, string PrimaryExchangeOpt,
string MultiplierOpt, string ExpiryOpt, string RightOpt, string StrikeOpt, string SecIdTypeOpt, string SecIdOpt, string TradingClassOpt)
{
decimal strike = (StrikeOpt == "" ? 0 : decimal.Parse(StrikeOpt));
int multiplier = (MultiplierOpt == "" ? 0 : int.Parse(MultiplierOpt));
int lotSize = (LotSizeOpt == "" ? 1 : int.Parse(LotSizeOpt));
if (Math.Sign(lotSize) != 1)
{
throw new Exception(string.Format("Error, contract lot size must be positive! ({0})", lotSize));
}
var contracts = XLOM.GetAll <Contract>();
int contractId = contracts.Count() + 1;
Contract c = new Contract(contractId, Symbol, Type, Exchange, Currency,
PrimaryExchangeOpt, multiplier, ExpiryOpt, strike,
RightOpt, SecIdTypeOpt, SecIdOpt, TradingClassOpt, (PositiveInteger)lotSize);
Contract cc = contracts.Where(cs => cs.Value.Equals(c)).SingleOrDefault().Value;
if (cc != null)
{
return(XLOM.Key(cc));
}
else
{
return(XLOM.Add(string.Format("{0}({1})", Symbol, contractId), c));
}
}
public static object CreateMarketDataSource(string ContractHandle, object[] Timestamps, object[] MidPrices, object SpreadOpt)
{
double spread = Utils.GetOptionalParameter(SpreadOpt, 0.0);
Contract cc = XLOM.Get <Contract>(ContractHandle);
object[] timestamps = Utils.GetVector <object>(Timestamps);
double[] midprices = Utils.GetVector <double>(MidPrices);
int nData = timestamps.Length;
bool oaDate = (timestamps[0].GetType() == typeof(double));
List <ITimestampedDatum> DataSource = new List <ITimestampedDatum>(nData);
for (int i = 0; i < nData; ++i)
{
DateTimeOffset now = new DateTimeOffset((oaDate ? DateTime.FromOADate((double)timestamps[i]) : DateTime.Parse(timestamps[i].ToString())), new TimeSpan(0, 0, 0));
DataSource.Add(new Market(now, cc.Id, (decimal)(midprices[i] * (1 - 0.5 * spread)), (decimal)(midprices[i] * (1 + 0.5 * spread))));
}
DataSourceInfo dsi = new DataSourceInfo("InMemoryMarketDataSource", cc.Id, cc.Symbol);
if (XLOM.Contains(dsi.ToString()))
{
XLOM.Remove(dsi.ToString());
}
return(XLOM.Add(dsi.ToString(), DataSource));
}
public ObjectList()
{
InitializeComponent();
foreach (OMKey ok in XLOM.GetKeys())
{
xLOMBindingSource.Add(ok);
}
}
public static object[,] DisplayHandle(string Handle)
{
object o = XLOM.Get <object>(Handle);
Type t = o.GetType();
if (o is IEnumerable <double> )
{
IEnumerable <double> oo = o as IEnumerable <double>;
object[,] data = new object[oo.Count(), 1];
int i = 0;
foreach (double dd in oo)
{
data[i++, 0] = dd;
}
return(data);
}
else if (t == typeof(double[, ]))
{
double[,] oo = o as double[, ];
int nRows = oo.GetLength(0), nCols = oo.GetLength(1);
object[,] data = new object[nRows, nCols];
for (int i = 0; i < nRows; ++i)
{
for (int j = 0; j < nCols; ++j)
{
data[i, j] = oo[i, j];
}
}
return(data);
}
else if (t == typeof(object[, ]))
{
return(o as object[, ]);
}
else if (o is IEnumerable <ITimestampedDatum> )
{
IEnumerable <ITimestampedDatum> oo = o as IEnumerable <ITimestampedDatum>;
object[,] data = new object[oo.Count(), 1];
int i = 0;
foreach (ITimestampedDatum dd in oo)
{
data[i++, 0] = dd.ToString();
}
return(data);
}
throw new Exception("Error, unrecognised handle type in DisplayHandle()!");
}
public void DisconnectData(int TopicId)
{
int contractId = TopicsToContractIds[TopicId];
TopicsToContractIds.Remove(TopicId);
TopicsToTickTypes.Remove(TopicId);
if (TopicsToContractIds.Where(x => x.Value == contractId).Count() == 0)
{
XL_RTD.DataSource.Unsubscribe(XLOM.GetById <Contract>(contractId));
}
}
public static string DeleteHandle(object[] HandleNames)
{
int removed = 0;
foreach (string s in HandleNames)
{
if (XLOM.Remove(s))
{
removed++;
}
}
return(removed + " objects deleted");
}
public static object GetOptionStrikes(string Underlying, string Expiry)
{
string omKey = Underlying + "_" + Expiry + "_Strikes";
if (XLOM.Contains(omKey))
{
return(XLOM.Get(omKey));
}
object[,] ret = getOptionStrikes(Underlying, Expiry).ToRange();
XLOM.Add(omKey, ret);
return(ret);
}
public object ConnectData(int TopicId, ref Array Strings, ref bool GetNewValues)
{
if (Strings.Length != 2)
{
throw new Exception("Error: Must supply ContractId and TickType!");
}
string contractKey = Strings.GetValue(0) as string; // The key in OM.
Contract cc = XLOM.Get <Contract>(contractKey);
int contractId = cc.Id;
string contractCcy = cc.Currency;
IBTickType tickType = (IBTickType)Enum.Parse(typeof(IBTickType), Strings.GetValue(1) as string, true);
// If we're not already subscribed to this contract, crank up TWSSource.
if (!(TopicsToContractIds.ContainsKey(TopicId) && TopicsToTickTypes.ContainsKey(TopicId)))
{
TopicsToContractIds.Add(TopicId, contractId);
TopicsToTickTypes.Add(TopicId, tickType);
if (!ContractsToMarkets.ContainsKey(contractId))
{
ContractsToMarkets.Add(contractId, new Market(DateTimeOffset.UtcNow, contractId, -1, -1));
ContractsUpdated.Add(contractId, true);
XL_RTD.DataSource.Subscribe(cc);
XL_RTD.Ether.AsObservable <Market>().Where(x => x.ContractId == contractId).Subscribe(x =>
{
ContractsToMarkets[x.ContractId] = x;
ContractsUpdated[x.ContractId] = true;
try
{
RTDCallback.UpdateNotify();
}
catch (Exception e)
{
Debug.WriteLine(e.StackTrace);
}
},
e =>
{
Debug.WriteLine(e.ToString());
});
}
}
return("(WAITING)");
}
public static SerialisableDictionary CreateConfigDictionary(object[,] ConfigMatrix)
{
SerialisableDictionary cd = new SerialisableDictionary();
int r = ConfigMatrix.GetLength(0);
int c = ConfigMatrix.GetLength(1);
if (c == 2)
{
for (int x = 0; x < r; ++x)
{
if (!ConfigMatrix[x, 0].Equals(ExcelDna.Integration.ExcelEmpty.Value))
{
if (XLOM.Contains(ConfigMatrix[x, 1].ToString(), true))
{
cd[ConfigMatrix[x, 0].ToString()] = XLOM.Get(ConfigMatrix[x, 1].ToString());
}
else
{
cd[ConfigMatrix[x, 0].ToString()] = ConfigMatrix[x, 1];
}
}
}
}
else if (c > 2)
{
for (int cc = 1; cc < c; ++cc)
{
string colName = ConfigMatrix[0, cc].ToString();
Dictionary <string, object> cdd = new Dictionary <string, object>();
for (int x = 1; x < r; ++x)
{
cdd[ConfigMatrix[x, 0].ToString()] = ConfigMatrix[x, cc];
}
cd.Add(colName, cdd);
}
}
return(cd);
}
public static object CreateKeyValueMap(object[] Keys, object[] Values, object HandleNameOpt)
{
string handleName = Utils.GetOptionalParameter(HandleNameOpt, "KeyValueMap");
string[] keys = Utils.GetVector <string>(Keys);
object[] values = Utils.GetVector <object>(Values);
Dictionary <string, object> map = new Dictionary <string, object>();
for (int i = 0; i < keys.Length; ++i)
{
if (XLOM.Contains(values[i].ToString(), true))
{
map.Add(keys[i], XLOM.Get(values[i].ToString()));
}
else
{
map.Add(keys[i], values[i]);
}
}
return(XLOM.Add(handleName, map));
}
public ObjectDisplayForm(string key)
{
InitializeComponent();
object oo = XLOM.Get(key);
var serializer = new DataContractSerializer(oo.GetType());
string xmlString;
using (var sw = new StringWriter())
{
using (var writer = new XmlTextWriter(sw))
{
writer.Formatting = Formatting.Indented;
serializer.WriteObject(writer, oo);
writer.Flush();
xmlString = sw.ToString();
}
}
var xd = new XmlDocument();
xd.LoadXml(xmlString);
treeView1.Nodes.Clear();
treeView1.Nodes.Add(new TreeNode(xd.DocumentElement.Name));
TreeNode tNode = new TreeNode();
tNode = treeView1.Nodes[0];
AddNode(xd.DocumentElement, tNode);
tNode.Expand();
//treeView1.ExpandAll();
}
public static object CreateDepositRateDataSource(string Currency, object[] Timestamps, double[] DepositRates)
{
int nData = Timestamps.Length;
bool oaDate = (Timestamps[0].GetType() == typeof(double));
List <ITimestampedDatum> DataSource = new List <ITimestampedDatum>(nData);
for (int i = 0; i < nData; ++i)
{
DateTimeOffset now = new DateTimeOffset((oaDate ? DateTime.FromOADate((double)Timestamps[i]) :
DateTime.Parse(Timestamps[i].ToString())), new TimeSpan(0, 0, 0));
DataSource.Add(new DepositRate(now, Currency, DepositRates[i]));
}
DataSourceInfo dsi = new DataSourceInfo("InMemoryDepositRateDataSource", 0, Currency);
if (XLOM.Contains(dsi.ToString()))
{
XLOM.Remove(dsi.ToString());
}
return(XLOM.Add(dsi.ToString(), DataSource));
}
public static object CreateImpliedVolDataSource(int ContractId, object[] Timestamps, double[] ImpliedVols)
{
int nData = Timestamps.Length;
bool oaDate = (Timestamps[0].GetType() == typeof(double));
List <ITimestampedDatum> DataSource = new List <ITimestampedDatum>(nData);
for (int i = 0; i < nData; ++i)
{
DateTimeOffset now = new DateTimeOffset((oaDate ? DateTime.FromOADate((double)Timestamps[i]) :
DateTime.Parse(Timestamps[i].ToString())), new TimeSpan(0, 0, 0));
DataSource.Add(new ImpliedVol(now, ContractId, ImpliedVols[i]));
}
DataSourceInfo dsi = new DataSourceInfo("InMemoryImpliedVolDataSource", ContractId, "ATMF");
if (XLOM.Contains(dsi.ToString()))
{
XLOM.Remove(dsi.ToString());
}
return(XLOM.Add(dsi.ToString(), DataSource));
}
public static void XLOMSave()
{
XLOM.Serialise();
}
public static object LoadFixings(string ticker, object BaseURLOpt, object ForceReloadOpt)
{
string baseURl = Utils.GetOptionalParameter(BaseURLOpt, @"http://cg028486/jupiter/data/fixings");
bool forceReloadOpt = Utils.GetOptionalParameter(ForceReloadOpt, false);
string key = ticker + "_fixings";
SortedDictionary <DateTime, Tuple <double, double, double, double, string> > fixings = XLOM.Get <SortedDictionary <DateTime, Tuple <double, double, double, double, string> > >(key);
if (forceReloadOpt || fixings == null)
{
fixings = new SortedDictionary <DateTime, Tuple <double, double, double, double, string> >();
// Eg, http://cg028486/jupiter/data/fixings/ES1.xml
string url = string.Format(@"{0}/{1}.xml", baseURl, ticker);
XmlDocument xml = new XmlDocument();
xml.Load(new XmlTextReader(url));
var fixingNodes = xml.DocumentElement.SelectNodes("FixingSchedules/FixingSchedule/Fixings/EqFixing");
foreach (XmlNode fixingNode in fixingNodes)
{
DateTime date = DateTime.Parse(fixingNode["Date"].InnerText);
string comment = fixingNode["Comments"].InnerText;
double open = -1;
double.TryParse(fixingNode["Open"].InnerText, out open);
double low = -1;
double.TryParse(fixingNode["Low"].InnerText, out low);
double high = -1;
double.TryParse(fixingNode["High"].InnerText, out high);
double close = -1;
double.TryParse(fixingNode["Close"].InnerText, out close);
if (close != 0)
{
fixings.Add(date, new Tuple <double, double, double, double, string>(open, low, high, close, comment));
}
}
XLOM.Add(ticker + "_fixings", fixings);
}
object[,] ret = new object[fixings.Count + 1, 6];
ret[0, 0] = "Date";
ret[0, 1] = "Open";
ret[0, 2] = "Low";
ret[0, 3] = "High";
ret[0, 4] = "Close";
ret[0, 5] = "Comment";
int i = 1;
foreach (var fixing in fixings)
{
ret[i, 0] = fixing.Key;
ret[i, 1] = fixing.Value.Item1;
ret[i, 2] = fixing.Value.Item2;
ret[i, 3] = fixing.Value.Item3;
ret[i, 4] = fixing.Value.Item4;
ret[i, 5] = fixing.Value.Item5;
i++;
}
return(ret);
}
public static object ZEval(object[] Expressions, string StartDate, object EndDateOpt,
[ExcelArgument(Description = "Use 'TR' to attempt to use total return indices. BBG limited to 5k data points.")]
object FieldOpt, object PeriodicityOpt,
object ReverseOpt, object NoLabelsOpt, object[] NamesOpt)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return("#N/A");
}
string[] expressionSet = Utils.GetVector <string>(Expressions);
string field = Utils.GetOptionalParameter(FieldOpt, "PX_LAST");
if (field.ToLower().StartsWith("tr"))
{
field = "TOT_RETURN_INDEX_NET_DVDS";
}
string periodicity = Utils.GetOptionalParameter(PeriodicityOpt, "Daily").ToUpper();
string startDate = Utils.GetDate(StartDate, DateTime.Now).ToString("yyyyMMdd");
string endDate = Utils.GetDate(EndDateOpt, DateTime.Now).ToString("yyyyMMdd");
bool reverse = Utils.GetOptionalParameter(ReverseOpt, false);
bool noLabels = Utils.GetOptionalParameter(NoLabelsOpt, false);
string[] names = Utils.GetVector <string>(NamesOpt);
Stopwatch sw1 = new Stopwatch();
sw1.Start();
// Identify tickers.
Regex regex = new Regex(@"(?<=\{).*?(?=\})");
Dictionary <string, string> tickersToVariables = new Dictionary <string, string>();
// We're going to decorate naked expressions with curlies UNLESS the expression set contains '=' signs, which signify
// variable declarations.
bool needsBracesDefault = true;
if (expressionSet.Where(x => x.Contains('=')).Count() > 0)
{
needsBracesDefault = false;
}
int i = 0;
foreach (string expression in expressionSet)
{
bool needsBraces = needsBracesDefault;
foreach (Match match in regex.Matches(expression))
{
string ticker = match.Value;
if (ticker.Contains("r@") || ticker.Contains("d@") || ticker.Contains("l@"))
{
ticker = ticker.Replace("r@", "");
ticker = ticker.Replace("d@", "");
ticker = ticker.Replace("l@", "");
}
needsBraces = false;
if (tickersToVariables.ContainsKey(ticker))
{
continue;
}
else
{
tickersToVariables[ticker] = "group" + (i++);
}
}
// If no matches found, assume the whole expression is one ticker.
if (needsBraces)
{
expressionSet[i] = "{" + expression + "}";
tickersToVariables[expression] = "group" + (i++);
}
}
// Assemble raw data.
string[] tickersToRetrieve = tickersToVariables.Keys.Where(x =>
{
string key = MakeKey(x, startDate, endDate, field, periodicity);
return(!XLOM.Contains(key));
}).ToArray();
if (tickersToRetrieve.Length != 0)
{
bool ok = GetBBGData(tickersToRetrieve, startDate, endDate, field, periodicity);
if (!ok)
{
throw new Exception("Error retrieving data from Bloomberg!");
}
}
Debug.Write("Got data in " + sw1.ElapsedMilliseconds + "ms; ");
sw1.Restart();
Dictionary <string, SortedDictionary <DateTime, double> > data = new Dictionary <string, SortedDictionary <DateTime, double> >();
SortedSet <DateTime> dates = new SortedSet <DateTime>();
DateTime firstDate = new DateTime(9999, 1, 1);
foreach (string ticker in tickersToVariables.Keys)
{
string key = MakeKey(ticker, startDate, endDate, field, periodicity);
data[ticker] = XLOM.Get(key) as SortedDictionary <DateTime, double>;
DateTime startDate1 = data[ticker].Keys.FirstOrDefault();
if (startDate1 < firstDate)
{
firstDate = startDate1;
}
if (data[ticker] != null)
{
dates.UnionWith(data[ticker].Keys.Where(x => x >= firstDate));
}
}
dates.RemoveWhere(x => x < firstDate);
Debug.Write("assembled data in " + sw1.ElapsedMilliseconds + "ms; ");
sw1.Restart();
return(ZEvalOnData(expressionSet, reverse, noLabels, names, sw1, tickersToVariables, data, dates));
}
private static bool GetBBGData(string[] tickers, string startDate, string endDate, string field, string periodicity)
{
session = new Session();
bool sessionStarted = session.Start();
if (!sessionStarted || !session.OpenService("//blp/refdata"))
{
return(false);
}
Service refDataService = session.GetService("//blp/refdata");
Request request = refDataService.CreateRequest("HistoricalDataRequest");
Element securities = request.GetElement("securities");
foreach (string t in tickers)
{
securities.AppendValue(t.ToUpper());
}
Element fields = request.GetElement("fields");
fields.AppendValue(field);
request.Set("periodicityAdjustment", "ACTUAL");
request.Set("periodicitySelection", periodicity);
request.Set("nonTradingDayFillOption", "NON_TRADING_WEEKDAYS");
if (periodicity == "DAILY")
{
request.Set("calendarCodeOverride", "5d");
}
request.Set("startDate", startDate);
if (endDate != string.Empty)
{
request.Set("endDate", endDate);
}
request.Set("returnEids", true);
session.SendRequest(request, null);
Dictionary <string, SortedDictionary <DateTime, double> > data = new Dictionary <string, SortedDictionary <DateTime, double> >();
while (true)
{
Event eventObj = session.NextEvent();
foreach (Message msg in eventObj)
{
if (msg.HasElement("securityData"))
{
string ticker = msg["securityData"]["security"].GetValue() as string;
if (!data.ContainsKey(ticker))
{
data[ticker] = new SortedDictionary <DateTime, double>();
}
int nData = msg["securityData"]["fieldData"].NumValues;
for (int j = 0; j < nData; ++j)
{
Element oo = (Element)msg.AsElement["securityData"]["fieldData"][j];
DateTime dt = oo["date"].GetValueAsDatetime().ToSystemDateTime();
if (oo.HasElement(field))
{
data[ticker][dt] = oo[field].GetValue().AsDouble();
}
}
}
}
if (eventObj.Type == Event.EventType.RESPONSE)
{
break;
}
}
if (data.Count == 0)
{
return(false);
}
foreach (string k in data.Keys)
{
string key = MakeKey(k, startDate, endDate, field, periodicity);
XLOM.Add(key, data[k]);
}
return(true);
}
public static object GetOptionTicker(string Underlying, string Expiry, double Strike, int PutOrCall)
{
DateTime expiry;
if (!DateTime.TryParse(Expiry, out expiry))
{
expiry = DateTime.FromOADate(int.Parse(Expiry));
}
string type = Underlying.Split(' ').Last();
string omKey = Underlying + "_OptionTickerTemplate";
if (XLOM.Contains(omKey))
{
string ticker = XLOM.Get(omKey).ToString().Replace("___TYPE___", PutOrCall == -1 ? " P" : " C")
.Replace("___STRIKE___", Strike.ToString())
.Replace("___EXPIRY___", expiry.ToString("MM/dd/yy"));
return(ticker);
}
session = new Session();
bool sessionStarted = session.Start();
if (!sessionStarted || !session.OpenService("//blp/refdata"))
{
throw new Exception("Failed to connect to Bloomberg!");
}
Service refDataService = session.GetService("//blp/refdata");
Request request = refDataService.CreateRequest("ReferenceDataRequest");
Element securities = request.GetElement("securities");
securities.AppendValue(Underlying);
Element fields = request.GetElement("fields");
fields.AppendValue("CHAIN_FULL");
session.SendRequest(request, null);
while (true)
{
Event eventObj = session.NextEvent();
foreach (Message msg in eventObj)
{
if (msg.HasElement("securityData"))
{
Element securityData = msg["securityData"].GetValueAsElement(0);
Element structureData = (Element)securityData["fieldData"].GetElement(0);
for (int i = 0; i < structureData.NumValues; ++i)
{
double strike = ((Element)structureData[i])["Strike"].GetValueAsFloat64();
string expiration = ((Element)structureData[i])["Expiration"].GetValueAsString();
DateTime dt = DateTime.Parse(expiration);
if (dt == expiry && strike == Strike)
{
string ticker;
if (PutOrCall == -1)
{
ticker = ((Element)structureData[i])["Put Ticker"].GetValueAsString() + " " + type;
}
else
{
ticker = ((Element)structureData[i])["Call Ticker"].GetValueAsString() + " " + type;
}
string template = ticker.Replace(dt.ToString("MM/dd/yy"), "___EXPIRY___").Replace(strike.ToString(), "___STRIKE___");
template = template.Replace(PutOrCall == -1 ? " P" : " C", " ___TYPE___");
XLOM.Add(omKey, template);
return(ticker);
}
}
}
}
if (eventObj.Type == Event.EventType.RESPONSE)
{
break;
}
}
return("Error, no such listed option!");
}
public static object ZDH(object[] Tickers, string StartDate, object EndDateOpt, object FieldOpt, object PeriodicityOpt,
object ReverseOpt, object NoLabelsOpt, object[] NamesOpt, object NormaliseOpt)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return("#N/A");
}
string[] tickers = Utils.GetVector <string>(Tickers);
string periodicity = Utils.GetOptionalParameter(PeriodicityOpt, "Daily").ToUpper();
string field = Utils.GetOptionalParameter(FieldOpt, "PX_LAST");
if (field.ToLower().StartsWith("tr"))
{
field = "TOT_RETURN_INDEX_NET_DVDS";
}
string startDate = Utils.GetDate(StartDate, DateTime.Now).ToString("yyyyMMdd");
string endDate = Utils.GetDate(EndDateOpt, DateTime.Now).ToString("yyyyMMdd");
bool reverse = Utils.GetOptionalParameter(ReverseOpt, false);
bool noLabels = Utils.GetOptionalParameter(NoLabelsOpt, false);
string[] names = Utils.GetVector <string>(NamesOpt);
bool normalise = Utils.GetOptionalParameter(NormaliseOpt, false);
// Retrieve required data.
string[] tickersToRetrieve = tickers.Where(x =>
{
string key = MakeKey(x, startDate, endDate, field, periodicity);
return(!XLOM.Contains(key));
}).ToArray();
if (tickersToRetrieve.Length != 0)
{
bool ok = GetBBGData(tickersToRetrieve, startDate, endDate, field, periodicity);
if (!ok)
{
throw new Exception("Error retrieving data from Bloomberg!");
}
}
// Assemble data.
Dictionary <string, SortedDictionary <DateTime, double> > data = new Dictionary <string, SortedDictionary <DateTime, double> >();
SortedSet <DateTime> dates = new SortedSet <DateTime>();
foreach (string ticker in tickers)
{
string key = MakeKey(ticker, startDate, endDate, field, periodicity);
data[ticker] = XLOM.Get(key) as SortedDictionary <DateTime, double>;
if (data[ticker] != null)
{
dates.UnionWith(data[ticker].Keys);
}
}
object[,] ret = new object[dates.Count + (noLabels ? 0 : 1), tickers.Length + (noLabels ? 0 : 1)];
ret[0, 0] = "Date";
double[] normalisationFactors = new double[tickers.Length];
for (int x = 0; x < tickers.Length; ++x)
{
normalisationFactors[x] = normalise ? data[tickers[x]].First().Value : 1;
}
int i = 1;
foreach (DateTime date in (reverse ? dates.Reverse() : dates))
{
ret[i, 0] = date;
int j = 0;
foreach (string ticker in tickers)
{
if (i == 1 && !noLabels)
{
ret[0, j + 1] = (names.Length == 0 ? ticker : names[j]);
}
if (data[ticker].ContainsKey(date))
{
ret[i, j + 1] = data[ticker][date] / normalisationFactors[j];
}
else
{
ret[i, j + 1] = data[ticker].Where(x => x.Key < date).OrderBy(x => x.Key).LastOrDefault().Value / normalisationFactors[j];
}
j++;
}
i++;
}
return(ret);
}
public static void XLOMLoad()
{
XLOM.Deserialise();
}
public static object GetOptionExpiries(string Underlying, object ExpiryTypeOpt)
{
string expirytype = Utils.GetOptionalParameter(ExpiryTypeOpt, string.Empty);
string omKey = Underlying + "_" + expirytype + "_Expiries";
if (XLOM.Contains(omKey))
{
return(XLOM.Get(omKey));
}
session = new Session();
bool sessionStarted = session.Start();
if (!sessionStarted || !session.OpenService("//blp/refdata"))
{
return("Failed to connect to Bloomberg!");
}
Service refDataService = session.GetService("//blp/refdata");
Request request = refDataService.CreateRequest("ReferenceDataRequest");
Element securities = request.GetElement("securities");
securities.AppendValue(Underlying);
Element fields = request.GetElement("fields");
fields.AppendValue("CHAIN_STRUCTURE");
session.SendRequest(request, null);
List <DateTime> expiries = new List <DateTime>();
while (true)
{
Event eventObj = session.NextEvent();
foreach (Message msg in eventObj)
{
if (msg.HasElement("securityData"))
{
Element securityData = msg["securityData"].GetValueAsElement(0);
Element structureData = (Element)securityData["fieldData"].GetElement(0);
for (int i = 0; i < structureData.NumValues; ++i)
{
string expiry = ((Element)structureData[i])["Expiration"].GetValueAsString();
DateTime dt = DateTime.Parse(expiry);
if (expirytype == string.Empty || expirytype == "All" || ((Element)structureData[i])["Periodicity"].GetValueAsString() == expirytype)
{
expiries.Add(dt);
}
}
}
}
if (eventObj.Type == Event.EventType.RESPONSE)
{
break;
}
}
object[,] ret = expiries.Distinct().Select(x => x.ToOADate()).OrderBy(x => x).ToArray().ToRange();
XLOM.Add(omKey, ret);
return(ret);
}
public static void ClearMemory()
{
XLOM.Reset();
}
public static void ClearMemoryAndRecalculate()
{
XLOM.Reset();
XlCall.Excel(XlCall.xlcCalculateNow);
}
public static object GetContractId(string ContractHandle)
{
Contract cc = XLOM.Get <Contract>(ContractHandle);
return(cc.Id);
}
public static object GetImpliedForwardCurve(string Underlying, object ExcludeZeroVolumeOptions)
{
bool excludeOnZeroVolume = Utils.GetOptionalParameter <bool>(ExcludeZeroVolumeOptions, false);
string omKey = Underlying + "_ForwardFactors";
if (XLOM.Contains(omKey))
{
return(XLOM.Get(omKey));
}
SortedDictionary <DateTime, SummaryContainer> forwardFactors = new SortedDictionary <DateTime, SummaryContainer>();
string type = Underlying.Split(' ').Last();
// Get call and put tickers.
session = new Session();
bool sessionStarted = session.Start();
if (!sessionStarted || !session.OpenService("//blp/refdata"))
{
return("Failed to connect to Bloomberg!");
}
Service refDataService = session.GetService("//blp/refdata");
Request request = refDataService.CreateRequest("ReferenceDataRequest");
Element securities = request.GetElement("securities");
securities.AppendValue(Underlying);
Element fields = request.GetElement("fields");
fields.AppendValue("PX_LAST");
fields.AppendValue("CHAIN_FULL");
session.SendRequest(request, null);
double spot = 0;
List <string> tickers = new List <string>();
while (true)
{
Event eventObj = session.NextEvent();
foreach (Message msg in eventObj)
{
if (msg.HasElement("securityData"))
{
Element securityData = msg["securityData"].GetValueAsElement(0);
if (securityData["fieldData"].HasElement("PX_LAST"))
{
spot = securityData["fieldData"].GetElement("PX_LAST").GetValueAsFloat64();
}
Element structureData = (Element)securityData["fieldData"].GetElement("CHAIN_FULL");
for (int i = 0; i < structureData.NumValues; ++i)
{
string expiration = ((Element)structureData[i])["Expiration"].GetValueAsString();
DateTime dt = DateTime.Parse(expiration);
if (!forwardFactors.ContainsKey(dt))
{
forwardFactors.Add(dt, new SummaryContainer());
}
double strike = ((Element)structureData[i])["Strike"].GetValueAsFloat64();
if (spot == 0 || Math.Abs(strike / spot - 1) < 0.3)
{
string call = ((Element)structureData[i])["Call Ticker"].GetValueAsString();
tickers.Add(call);
}
}
}
}
if (eventObj.Type == Event.EventType.RESPONSE)
{
break;
}
}
// Get implied forwards.
if (tickers.Count > 0)
{
Request req2 = refDataService.CreateRequest("ReferenceDataRequest");
Element securities2 = req2.GetElement("securities");
tickers.ForEach(x => securities2.AppendValue(x + " " + type));
Element fields2 = req2.GetElement("fields");
fields2.AppendValue("OPT_EXPIRE_DT");
fields2.AppendValue("PARITY_FWD_MID");
fields2.AppendValue("VOLUME");
session.SendRequest(req2, null);
while (true)
{
Event eventObj = session.NextEvent();
foreach (Message msg in eventObj)
{
if (msg.HasElement("securityData"))
{
int numValues = msg["securityData"].NumValues;
for (int i = 0; i < numValues; ++i)
{
Element securityData = msg["securityData"].GetValueAsElement(i);
if (securityData.HasElement("fieldData") && securityData["fieldData"].NumElements > 0)
{
int volume = 0;
if (securityData["fieldData"].HasElement("VOLUME"))
{
volume = securityData["fieldData"].GetElement("VOLUME").GetValueAsInt32();
}
DateTime expiration = DateTime.Now;
if (securityData["fieldData"].HasElement("OPT_EXPIRE_DT"))
{
string exp = ((Element)securityData["fieldData"]).GetElement("OPT_EXPIRE_DT").GetValueAsString();
expiration = DateTime.Parse(exp);
}
Element structureData = (Element)securityData["fieldData"].GetElement("PARITY_FWD_MID");
if (structureData.NumValues > 0)
{
double forward = structureData.GetValueAsFloat64();
if (volume > 0 || !excludeOnZeroVolume)
{
forwardFactors[expiration].Add(forward / spot);
}
}
}
}
}
}
if (eventObj.Type == Event.EventType.RESPONSE)
{
break;
}
}
}
object[,] ret = new object[forwardFactors.Count, 2];
int j = 0;
foreach (KeyValuePair <DateTime, SummaryContainer> kv in forwardFactors)
{
ret[j, 0] = kv.Key;
if (kv.Value.Average == 0)
{
ret[j, 1] = ExcelDna.Integration.ExcelError.ExcelErrorNA;
}
else
{
ret[j, 1] = kv.Value.Average;
}
j++;
}
// Save the forward factors.
XLOM.Add(omKey, ret);
return(ret);
}
private void DeleteObject(object sender, DataGridViewRowCancelEventArgs e)
{
OMKey key = new OMKey((string)e.Row.Cells[0].Value, (string)e.Row.Cells[1].Value, (int)e.Row.Cells[2].Value);
XLOM.Remove(key.ToString());
}