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); }