public IQuotesDalGetTimeseriesValuesResult GetTimseriesValues(IQuotesDalGetTimeSeriesValuesParams getQuotesParams)
{
IQuotesDalGetTimeseriesValuesResult result = new QuotesDalCSVGetQuotesResult();
foreach (var t in getQuotesParams.Tickers)
{
string fileName = t + "." + getQuotesParams.TimeFrame.ToString() + ".csv";
string filePath = Path.Combine(_rootFolder, getQuotesParams.Country, fileName);
if (File.Exists(filePath))
{
using (StreamReader sr = new StreamReader(new FileStream(filePath, FileMode.Open)))
{
IQuotesData data = new BaseQuotesData();
data.Ticker = t;
data.TimeFrame = getQuotesParams.TimeFrame;
data.Country = getQuotesParams.Country;
using (CsvHelper.CsvReader reader = new CsvHelper.CsvReader(sr))
{
reader.Read();
reader.ReadHeader();
while (reader.Read())
{
ITimeSeriesRecord record = new BaseQuotesRecord();
record.Time = reader.GetField <DateTime>("Time");
record["Open"] = reader.GetField <decimal>("Open");
record["High"] = reader.GetField <decimal>("High");
record["Low"] = reader.GetField <decimal>("Low");
record["Close"] = reader.GetField <decimal>("Close");
record["Volume"] = reader.GetField <decimal>("Volume");
if (record.Time <= getQuotesParams.PeriodEnd && record.Time >= getQuotesParams.PeriodStart)
{
data.AddRecord(record);
}
}
}
result.Quotes.Add(data);
}
}
else
{
result.AddError(Interfaces.EErrorCodes.QuotesNotFound, Interfaces.EErrorType.Warning, string.Format("Quotes not found: {0}, {1}", t, getQuotesParams.TimeFrame));
}
}
if (result.Quotes.Count == 0 && getQuotesParams.Tickers.Count != 0)
{
result.Success = false;
}
else
{
result.Success = true;
}
return(result);
}
private void PrepareStatData(IPortfolioBuilderBuildParams builderParams)
{
int nInstruments = builderParams.Instruments.Count;
rets = new double[nInstruments];
stDevs = new double[nInstruments];
matCov = new double[nInstruments, nInstruments];
// getting instruments and calculating avg returns and stdevs for given time period
IQuotesDalGetTimeSeriesValuesParams getQuotesParams = _quotesDal.CreateGetQuotesParams();
getQuotesParams.PeriodStart = builderParams.PeriodStart;
getQuotesParams.PeriodEnd = builderParams.PeriodEnd;
getQuotesParams.TimeFrame = (QuotesInterfaces.ETimeFrame)builderParams.TimeFrame;
getQuotesParams.Tickers.AddRange(builderParams.Instruments);
IQuotesDalGetTimeseriesValuesResult getQuotesResult = _quotesDal.GetTimseriesValues(getQuotesParams);
Dictionary <string, List <double> > returns = new Dictionary <string, List <double> >();
// calculating returns & stdev
for (int i = 0; i < nInstruments; ++i)
{
double avgRet = 0;
returns.Add(getQuotesResult.Quotes[i].Ticker, new List <double>());
for (int d = 0; d < getQuotesResult.Quotes[i].Quotes.Count(); ++d)
{
double ret = (double)(getQuotesResult.Quotes[i].Quotes[d]["Close"] - getQuotesResult.Quotes[i].Quotes[d]["Open"]) / (double)getQuotesResult.Quotes[i].Quotes[d]["Open"];
returns[getQuotesResult.Quotes[i].Ticker].Add(ret);
avgRet += ret;
}
avgRet /= getQuotesResult.Quotes[i].Quotes.Count() - 1;
switch (builderParams.TimeFrame)
{
case PortfolioInterfaces.ETimeFrame.Monthly:
avgRet = Math.Pow(1 + avgRet, 12) - 1;
break;
}
rets[i] = avgRet;
stDevs[i] = MathNet.Numerics.Statistics.Statistics.PopulationStandardDeviation(returns[getQuotesResult.Quotes[i].Ticker]);
switch (builderParams.TimeFrame)
{
case PortfolioInterfaces.ETimeFrame.Monthly:
stDevs[i] *= Math.Sqrt(12);
break;
}
}
// calculating covariances
for (int k = 0; k < returns.Keys.Count; ++k)
{
for (int o = k; o < returns.Keys.Count; ++o)
{
matCov[k, o] = MathNet.Numerics.Statistics.Statistics.PopulationCovariance(returns[returns.Keys.ElementAt(k)], returns[returns.Keys.ElementAt(o)]);
matCov[o, k] = matCov[k, o];
}
}
}
public void GetQuotes_Single_Failed()
{
IQuotesDal dal = PrepareQuotesDal();
IQuotesDalGetTimeSeriesValuesParams getParams = dal.CreateGetQuotesParams();
getParams.Country = ConfigurationManager.AppSettings["CountryUS"];
getParams.Tickers.Add(ConfigurationManager.AppSettings["InvalidTicker"]);
getParams.TimeFrame = ETimeFrame.Monthly;
IQuotesDalGetTimeseriesValuesResult result = dal.GetTimseriesValues(getParams);
Assert.IsFalse(result.Success);
Assert.IsNotNull(result.Quotes);
Assert.AreEqual(result.Quotes.Count, 0);
}
public void GetQuotes_Single_Success()
{
IQuotesDal dal = PrepareQuotesDal();
IQuotesDalGetTimeSeriesValuesParams getParams = dal.CreateGetQuotesParams();
getParams.Country = ConfigurationManager.AppSettings["CountryUS"];
getParams.Tickers.Add(ConfigurationManager.AppSettings["TickerSPY"]);
getParams.TimeFrame = ETimeFrame.Monthly;
IQuotesDalGetTimeseriesValuesResult result = dal.GetTimseriesValues(getParams);
Assert.IsTrue(result.Success);
Assert.IsNotNull(result.Quotes);
Assert.AreEqual(result.Quotes.Count, 1);
Assert.AreEqual(result.Quotes[0].Ticker, ConfigurationManager.AppSettings["TickerSPY"]);
Assert.AreEqual(result.Quotes[0].Country, ConfigurationManager.AppSettings["CountryUS"]);
Assert.AreNotEqual(result.Quotes[0].Quotes.Count(), 0);
}
public IQuotesDalGetTimeseriesValuesResult GetTimseriesValues(IQuotesDalGetTimeSeriesValuesParams getQuotesParams)
{
IQuotesDalGetTimeseriesValuesResult result = new QuotesDalMSSQLGetQuotesResult();
string spName = "[SP_Get_Ticker_Timeseries_Values]";
SqlConnection conn = OpenConnection("ConnectionStringTimeSeries");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = schema + "." + spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlParameter paramTypeId = new SqlParameter("@IN_Period_Type_Id", SqlDbType.Int, 0, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, (int)getQuotesParams.TimeFrame);
SqlParameter paramTickerId = new SqlParameter("@IN_Ticker_Id", SqlDbType.Int, 0, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, 0);
cmd.Parameters.Add(paramTypeId);
cmd.Parameters.Add(paramTickerId);
for (int i = 0; i < getQuotesParams.Tickers.Count; ++i)
{
// getting ID of the current ticker
paramTickerId.Value = GetTickerId(getQuotesParams.Tickers[i], conn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
try
{
da.Fill(ds);
if (ds.Tables.Count >= 1)
{
BaseQuotesData qdata = new BaseQuotesData();
qdata.Ticker = getQuotesParams.Tickers[i];
qdata.Country = getQuotesParams.Country;
qdata.TimeFrame = getQuotesParams.TimeFrame;
foreach (DataRow row in ds.Tables[0].Rows)
{
DateTime dtEvent = (DateTime)row[0];
if (dtEvent >= getQuotesParams.PeriodStart && dtEvent <= getQuotesParams.PeriodEnd)
{
int valCount = row.ItemArray.Count(x => !DBNull.Value.Equals(x));
ITimeSeriesRecord qrec = new CustomTimeseriesRecord(new List <string>(new string[valCount - 1]), dtEvent);
for (int r = 1; r <= valCount - 1; ++r)
{
qrec[r - 1] = (decimal)row[r];
}
qdata.AddRecord(qrec);
}
}
result.Quotes.Add(qdata);
}
}
catch (Exception ex)
{
result.Errors.Add(new Interfaces.Error()
{
Code = Interfaces.EErrorCodes.QuotesNotFound,
Type = Interfaces.EErrorType.Error,
Message = string.Format("'{0}, {1}': failed to read timeseries. SQL message: {2}", getQuotesParams.Tickers[i], getQuotesParams.TimeFrame, ex.Message)
});
}
}
if (result.Quotes.Count == 0 && getQuotesParams.Tickers.Count != 0)
{
result.Success = false;
}
else
{
result.Success = true;
}
conn.Close();
return(result);
}
public GetTimeSeriesResponse Any(GetTimeSeries request)
{
_logger.Log(EErrorType.Info, " ****** Call start: GetTimeSeries");
GetTimeSeriesResponse response = new GetTimeSeriesResponse();
TransferHeader(request, response);
try
{
if (IsValidSessionToken(request))
{
IQuotesDalGetTimeSeriesValuesParams getParams = _dal.CreateGetQuotesParams();
getParams.Country = request.CountryCode;
getParams.Tickers.Add(request.Ticker);
getParams.PeriodEnd = request.PeriodEnd != null ? (DateTime)request.PeriodEnd : DateTime.Now;
getParams.PeriodStart = request.PeriodStart != null ? (DateTime)request.PeriodStart : DateTime.Parse(ConfigurationManager.AppSettings["DefaultPeriodStart"]);
getParams.TimeFrame = (QuotesInterfaces.ETimeFrame)request.TimeFrame;
IQuotesDalGetTimeseriesValuesResult getResult = _dal.GetTimseriesValues(getParams);
// copying list of errors - there can be also warnings too
response.Errors.AddRange(response.Errors);
if (getResult.Success)
{
TranslateToTickerQuotes(response, getResult);
if (response.Payload.Values.Quotes.Count == 0)
{
response.Errors.Add(
new Error()
{
Code = EErrorCodes.EmptyCollection,
Type = EErrorType.Warning,
Message = string.Format("Data not present for {0}, Timeframe - {1}, Dates: {2} - {3}", request.Ticker, request.TimeFrame, getParams.PeriodStart, getParams.PeriodEnd)
});
}
response.Success = true;
}
else
{
response.Success = false;
response.Errors.AddRange(getResult.Errors);
}
}
else
{
response.Success = false;
response.Errors.Add(new Error()
{
Code = EErrorCodes.InvalidSession, Type = EErrorType.Error, Message = "Invalid session token"
});
}
}
catch (Exception ex)
{
_logger.Log(ex);
response.Success = false;
response.Errors.Add(new Error()
{
Code = EErrorCodes.GeneralError,
Type = EErrorType.Error,
Message = string.Format("Unpexcted error: {0}", ex.Message)
});
}
_logger.Log(EErrorType.Info, " ****** Call end: GetQuotes");
return(response);
}
