public static async Task<Tuple<IList<List<DailyData>>,TimeSpan, TimeSpan>> GetHistoricalAndRealtimesQuotesAsync(GeneralStrategyParameters p_generalParams, List<string> p_tickers, CancellationToken p_canc = default(CancellationToken))
{
//- SPY 300K CSV SQLqueryTime (local server), msecond times for (for Azure in-house datacenter, these will be less)
//All data: Open, High, Low, Close, Volume : 886, 706, 1237, 761, 727, Avg = 863
//Only ClosePrice: 662, 680, 702, 820, 663, 692, Avg = 703
// if the Website is not local, but it is in the same Azure datacenter as the SQL center
//SQL query time (All OHLCV data): msec: 612, 614, 667, 772, 632, 613, 665, 662, Avg = 654
//SQL query time (only Close data): msec: 623, 624, 704, 614, 615, 621, 621, 722, 636, Avg = 642
//Conclusion:downloading only Closeprice from SQL, we can save 100msec (LocalServer website (non-datacenter website)) or 15msec (Azure server website when SQL server is very close), still worth it
ushort sqlReturnedColumns = QuoteRequest.TDC; // QuoteRequest.All or QuoteRequest.TDOHLCVS
//var sqlReturnTask = GetHistQuotesAsync(p_tickers.Select(r => new QuoteRequest { Ticker = r, nQuotes = Int32.MaxValue, NonAdjusted = false, ReturnedColumns = sqlReturnedColumns }), HQCommon.AssetType.Stock, true); // Ascending date order: TRUE, better to order it at the SQL server than locally. SQL has indexers
var sqlReturnTask = GetHistQuotesAsync(p_generalParams, p_tickers, sqlReturnedColumns);
Task<Tuple<IList<double?>, TimeSpan>> realtimeReturnTask = null;
if (p_generalParams.endDateUtc >= DateTime.UtcNow)
realtimeReturnTask = GetRealtimesQuotesAsync(p_tickers);
// Control returns here before GetHistoricalQuotesAsync() returns. // ... Prompt the user.
Console.WriteLine("Please wait patiently while I do SQL and realtime price queries.");
// Wait for the tasks to complete. // ... Display its results.
//var combinedAsyncTasksResults = await Task.WhenAll(sqlReturnTask, realtimeReturnTask); this cannot be done now, because the return values are different
if (realtimeReturnTask != null)
await Task.WhenAll(sqlReturnTask, realtimeReturnTask); // otherwise, the next await will wait the historical data
var sqlReturnData = await sqlReturnTask; //as they have all definitely finished, you could also use Task.Value, "However, I recommend using await because it's clearly correct, while Result can cause problems in other scenarios."
Tuple<IList<double?>, TimeSpan> realtimeReturnData = null;
if (realtimeReturnTask != null)
realtimeReturnData = await realtimeReturnTask; //as they have all definitely finished, you could also use Task.Value, "However, I recommend using await because it's clearly correct, while Result can cause problems in other scenarios."
var sqlReturn = sqlReturnData.Item1;
List<List<DailyData>> returnQuotes = null;
// sql query of "VXX.SQ" gives back tickers of VXX and also tickers of "VXX.SQ"
int closePriceIndex = -1;
if (sqlReturnedColumns == QuoteRequest.TDOHLCVS)
closePriceIndex = 5;
else if (sqlReturnedColumns == QuoteRequest.TDC)
closePriceIndex = 2;
else
throw new NotImplementedException();
returnQuotes = p_tickers.Select(ticker =>
{
string tickerWithoutDotSQ = "";
if (ticker.EndsWith(".SQ"))
tickerWithoutDotSQ = ticker.Substring(0, ticker.Length - ".SQ".Length);
return sqlReturn.Where(row => (string)row[0] == ticker || (string)row[0] == tickerWithoutDotSQ).Select(
row => new DailyData()
{
Date = ((DateTime)row[1]),
ClosePrice = (double)Convert.ToDecimal(row[closePriceIndex]) // row[2] is object(double) if it is a stock (because Adjustment multiplier), and object(float) if it is Indices. However Convert.ToDouble(row[2]) would convert 16.66 to 16.6599999
}).ToList();
}).ToList();
if (realtimeReturnData != null)
{
var todayDate = DateTime.UtcNow.Date;
var realtimeReturn = realtimeReturnData.Item1;
for (int i = 0; i < p_tickers.Count(); i++)
{
if (realtimeReturn[i] != null)
{
int todayInd = returnQuotes[i].FindLastIndex(r => r.Date == todayDate);
if (todayInd == -1) // if it is missing
{
returnQuotes[i].Add(new DailyData() { Date = todayDate, ClosePrice = (double)realtimeReturn[i] });
}
else // if it is already in the array, overwrite it
{
returnQuotes[i][todayInd].ClosePrice = (double)realtimeReturn[i];
}
}
}
}
return new Tuple<IList<List<DailyData>>, TimeSpan, TimeSpan>(returnQuotes, sqlReturnData.Item2, (realtimeReturnData != null)?realtimeReturnData.Item2:TimeSpan.Zero);
}
public static async Task <string> GenerateQuickTesterResponse(GeneralStrategyParameters p_generalParams, string p_strategyName, string p_params)
{
Stopwatch stopWatchTotalResponse = Stopwatch.StartNew();
if (p_strategyName != "LETFDiscrepancy1" && p_strategyName != "LETFDiscrepancy2" && p_strategyName != "LETFDiscrepancy3")
{
return(null);
}
string strategyParams = p_params;
int ind = -1;
string etfPairs = null;
if (strategyParams.StartsWith("ETFPairs=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("ETFPairs=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
return(@"{ ""errorMessage"": ""Error: uriQuery.IndexOf('&') 2. Uri: " + strategyParams + @""" }");
}
etfPairs = strategyParams.Substring(0, ind);
strategyParams = strategyParams.Substring(ind + 1);
}
string rebalancingFrequency = null;
if (strategyParams.StartsWith("rebalancingFrequency=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("rebalancingFrequency=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
rebalancingFrequency = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
{
strategyParams = strategyParams.Substring(ind + 1);
}
else
{
strategyParams = "";
}
}
ind = etfPairs.IndexOf('-');
if (ind == -1)
{
return(@"{ ""errorMessage"": ""Error: cannot find tickers in : " + etfPairs + @""" }");
}
string bullishTicker = etfPairs.Substring(0, ind);
string bearishTicker = etfPairs.Substring(ind + 1);
// startDates
// URE: Feb 2, 2007
// SRS: Feb 1, 2007
// XIV: Nov 30, 2010
// VXX: Jan 30, 2009
// FAS: Nov 19, 2008
// FAZ: Nov 19, 2008
Stopwatch stopWatch = Stopwatch.StartNew();
var getAllQuotesTask = StrategiesCommon.GetHistoricalAndRealtimesQuotesAsync(p_generalParams, (new string[] { bullishTicker, bearishTicker }).ToList());
// Control returns here before GetHistoricalQuotesAsync() returns. // ... Prompt the user.
Console.WriteLine("Please wait patiently while I do SQL and realtime price queries.");
var getAllQuotesData = await getAllQuotesTask;
stopWatch.Stop();
string htmlNoteFromStrategy = "", noteToUserCheckData = "", noteToUserBacktest = "", debugMessage = "", errorMessage = "";
List <DailyData> pv = null;
if (String.Equals(p_strategyName, "LETFDiscrepancy1", StringComparison.InvariantCultureIgnoreCase))
{
pv = DoBacktestExample(getAllQuotesData.Item1, bullishTicker, bearishTicker, rebalancingFrequency);
}
else
{
pv = DoBacktestBasic(getAllQuotesData.Item1, bullishTicker, bearishTicker, p_strategyName, rebalancingFrequency, ref noteToUserCheckData, ref htmlNoteFromStrategy);
}
stopWatchTotalResponse.Stop();
StrategyResult strategyResult = StrategiesCommon.CreateStrategyResultFromPV(pv,
htmlNoteFromStrategy + ". " + noteToUserCheckData + "***" + noteToUserBacktest, errorMessage,
debugMessage + String.Format("SQL query time: {0:000}ms", getAllQuotesData.Item2.TotalMilliseconds) + String.Format(", RT query time: {0:000}ms", getAllQuotesData.Item3.TotalMilliseconds) + String.Format(", All query time: {0:000}ms", stopWatch.Elapsed.TotalMilliseconds) + String.Format(", TotalC#Response: {0:000}ms", stopWatchTotalResponse.Elapsed.TotalMilliseconds));
string jsonReturn = JsonConvert.SerializeObject(strategyResult);
return(jsonReturn);
//{
// "Name": "Apple",
// "Expiry": "2008-12-28T00:00:00",
// "Sizes": [
// "Small"
// ]
//}
//returnStr = "[" + String.Join(Environment.NewLine,
// (await Tools.GetHistoricalQuotesAsync(new[] {
// new QuoteRequest { Ticker = "VXX", nQuotes = 2, StartDate = new DateTime(2011,1,1), NonAdjusted = true },
// new QuoteRequest { Ticker = "SPY", nQuotes = 3 }
// }, HQCommon.AssetType.Stock))
// .Select(row => String.Join(",", row))) + "]";
//returnStr = returnStr.Replace(" 00:00:00", "");
//returnStr = returnStr.Replace("\n", ",");
//return @"[{""Symbol"":""VXX""},{""Symbol"":""^VIX"",""LastUtc"":""2015-01-08T19:25:48"",""Last"":17.45,""UtcTimeType"":""LastChangedTime""}]";
}
// if I have Get(), I cannot have GetAllRtp(), as it will be Duplicate resolve possibility and I got an exception.
//// IIS can handle if the return is a Task lst, not a HttpActionResult. It is needed for async SQL examples from Robert
public async Task<HttpResponseMessage> Get()
{
string jsonpCallback = null;
var response = this.Request.CreateResponse(HttpStatusCode.OK);
try
{
string uriQuery = this.Url.Request.RequestUri.Query; // "?s=VXX,XIV,^vix&f=ab&o=csv" from the URL http://localhost:58213/api/rtp?s=VXX,XIV,^vix&f=ab&o=csv
if (uriQuery.Length > 8192)
{//When you try to pass a string longer than 8192 charachters, a faultException will be thrown. There is a solution, but I don't want
throw new Exception("Error caught by WebApi Get():: uriQuery is longer than 8192: we don't process that. Uri: " + uriQuery);
}
uriQuery = uriQuery.Substring(1); // remove '?'
uriQuery = uriQuery.Replace("%20", " ").Replace("%5E", "^"); // de-coding from URL to normal things
int ind = -1;
if (uriQuery.StartsWith("jsonp=", StringComparison.InvariantCultureIgnoreCase))
{
uriQuery = uriQuery.Substring("jsonp=".Length);
ind = uriQuery.IndexOf('&');
if (ind == -1)
{
throw new Exception("Error: uriQuery.IndexOf('&') 2. Uri: " + uriQuery);
}
jsonpCallback = uriQuery.Substring(0, ind);
uriQuery = uriQuery.Substring(ind + 1);
}
if (!uriQuery.StartsWith("StartDate=", StringComparison.InvariantCultureIgnoreCase))
{
throw new Exception("Error: StartDate= was not found. Uri: " + uriQuery);
}
uriQuery = uriQuery.Substring("StartDate=".Length);
ind = uriQuery.IndexOf('&');
if (ind == -1)
{
ind = uriQuery.Length;
}
string startDateStr = uriQuery.Substring(0, ind);
if (ind < uriQuery.Length) // if we are not at the end of the string
uriQuery = uriQuery.Substring(ind + 1);
else
uriQuery = "";
if (!uriQuery.StartsWith("EndDate=", StringComparison.InvariantCultureIgnoreCase))
{
throw new Exception("Error: EndDate= was not found. Uri: " + uriQuery);
}
uriQuery = uriQuery.Substring("EndDate=".Length);
ind = uriQuery.IndexOf('&');
if (ind == -1)
{
ind = uriQuery.Length;
}
string endDateStr = uriQuery.Substring(0, ind);
if (ind < uriQuery.Length) // if we are not at the end of the string
uriQuery = uriQuery.Substring(ind + 1);
else
uriQuery = "";
if (!uriQuery.StartsWith("strategy=", StringComparison.InvariantCultureIgnoreCase))
{
throw new Exception("Error: strategy= was not found. Uri: " + uriQuery);
}
uriQuery = uriQuery.Substring("strategy=".Length);
ind = uriQuery.IndexOf('&');
if (ind == -1)
{
ind = uriQuery.Length;
}
string strategyName = uriQuery.Substring(0, ind);
if (ind < uriQuery.Length) // if we are not at the end of the string
uriQuery = uriQuery.Substring(ind + 1);
else
uriQuery = "";
string strategyParams = uriQuery;
DateTime startDate = DateTime.MinValue;
if (startDateStr.Length != 0)
{
if (!DateTime.TryParse(startDateStr, out startDate))
throw new Exception("Error: startDateStr couldn't be converted: " + uriQuery);
}
DateTime endDate = DateTime.MaxValue;
if (endDateStr.Length != 0)
{
if (!DateTime.TryParse(endDateStr, out endDate))
throw new Exception("Error: endDateStr couldn't be converted: " + uriQuery);
}
GeneralStrategyParameters generalParams = new GeneralStrategyParameters() { startDateUtc = startDate, endDateUtc = endDate };
string jsonString = (await VXX_SPY_Controversial.GenerateQuickTesterResponse(generalParams, strategyName, strategyParams));
if (jsonString == null)
jsonString = (await LEtfDistcrepancy.GenerateQuickTesterResponse(generalParams, strategyName, strategyParams));
if (jsonString == null)
jsonString = (await TotM.GenerateQuickTesterResponse(generalParams, strategyName, strategyParams));
if (jsonString == null)
throw new Exception("Strategy was not found in the WebApi: " + strategyName);
return ResponseBuilder(jsonpCallback, jsonString);
}
catch (Exception e)
{
return ResponseBuilder(jsonpCallback, @"{ ""errorMessage"": ""Exception caught by WebApi Get(): " + e.Message + @""" }");
}
}
//public static async Task<Tuple<IList<object[]>,TimeSpan>> GetHistQuotesAsync(IEnumerable<QuoteRequest> p_req, HQCommon.AssetType p_at, bool? p_isAscendingDates = null, CancellationToken p_canc = default(CancellationToken))
public static async Task<Tuple<List<object[]>, TimeSpan>> GetHistQuotesAsync(GeneralStrategyParameters p_generalParams, List<string> p_tickers, ushort p_sqlReturnedColumns)
{
List<string> stockTickers = p_tickers.Where(r => !r.StartsWith("^")).ToList();
List<string> indicesTickers = p_tickers.Where(r => r.StartsWith("^")).ToList();
TimeZoneInfo etZone = null;
int requestNQuotes = Int32.MaxValue;
DateTime? requestStartDateExcgLocal = null, requestEndDateExcgLocal = null;
if (p_generalParams.startDateUtc != DateTime.MinValue)
{
ConvertUtcToExchangeLocal(p_generalParams.startDateUtc, ref etZone, ref requestStartDateExcgLocal);
}
if (p_generalParams.endDateUtc != DateTime.MaxValue)
{
ConvertUtcToExchangeLocal(p_generalParams.endDateUtc, ref etZone, ref requestEndDateExcgLocal);
}
Stopwatch stopWatch = Stopwatch.StartNew();
Task<IList<object[]>> stocksSqlReturnTask = null, indicesSqlReturnTask = null; ;
IList<object[]> stocksSqlReturn = null, indicesSqlReturn = null;
if (stockTickers.Count != 0)
stocksSqlReturnTask = Tools.GetHistoricalQuotesAsync(stockTickers.Select(r => new QuoteRequest { Ticker = r, nQuotes = requestNQuotes, StartDate = requestStartDateExcgLocal, EndDate = requestEndDateExcgLocal, NonAdjusted = false, ReturnedColumns = p_sqlReturnedColumns }), HQCommon.AssetType.Stock, true); // Ascending date order: TRUE, better to order it at the SQL server than locally. SQL has indexers
if (indicesTickers.Count != 0)
indicesSqlReturnTask = Tools.GetHistoricalQuotesAsync(indicesTickers.Select(r => new QuoteRequest { Ticker = r, nQuotes = requestNQuotes, StartDate = requestStartDateExcgLocal, EndDate = requestEndDateExcgLocal, NonAdjusted = false, ReturnedColumns = p_sqlReturnedColumns }), HQCommon.AssetType.BenchmarkIndex, true); // Ascending date order: TRUE, better to order it at the SQL server than locally. SQL has indexers
if (stockTickers.Count != 0)
stocksSqlReturn = await stocksSqlReturnTask;
if (indicesTickers.Count != 0)
indicesSqlReturn = await indicesSqlReturnTask;
stopWatch.Stop();
TimeSpan historicalQueryTimeSpan = stopWatch.Elapsed;
List<object[]> sqlReturn = null;
if (stocksSqlReturn != null)
sqlReturn = stocksSqlReturn.ToList(); // the return is a List() already if you look deeper in the implementation
if (indicesSqlReturn != null)
{
if (sqlReturn == null)
sqlReturn = indicesSqlReturn.ToList();
else
sqlReturn.AddRange(indicesSqlReturn.ToList());
}
return new Tuple<List<object[]>, TimeSpan>(sqlReturn, historicalQueryTimeSpan);
}
public static async Task<string> GenerateQuickTesterResponse(GeneralStrategyParameters p_generalParams, string p_strategyName, string p_params)
{
Stopwatch stopWatchTotalResponse = Stopwatch.StartNew();
if (p_strategyName != "TotM")
return null;
string strategyParams = p_params;
int ind = -1;
string bullishTradingInstrument = null;
if (strategyParams.StartsWith("BullishTradingInstrument=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("BullishTradingInstrument=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
bullishTradingInstrument = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
string dailyMarketDirectionMaskSummerTotM = null;
if (strategyParams.StartsWith("DailyMarketDirectionMaskSummerTotM=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("DailyMarketDirectionMaskSummerTotM=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
dailyMarketDirectionMaskSummerTotM = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
string dailyMarketDirectionMaskSummerTotMM = null;
if (strategyParams.StartsWith("DailyMarketDirectionMaskSummerTotMM=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("DailyMarketDirectionMaskSummerTotMM=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
dailyMarketDirectionMaskSummerTotMM = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
string dailyMarketDirectionMaskWinterTotM = null;
if (strategyParams.StartsWith("DailyMarketDirectionMaskWinterTotM=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("DailyMarketDirectionMaskWinterTotM=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
dailyMarketDirectionMaskWinterTotM = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
string dailyMarketDirectionMaskWinterTotMM = null;
if (strategyParams.StartsWith("DailyMarketDirectionMaskWinterTotMM=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("DailyMarketDirectionMaskWinterTotMM=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
dailyMarketDirectionMaskWinterTotMM = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
//bullishTradingInstrument = bullishTradingInstrument.Replace("%20", " ");
ind = bullishTradingInstrument.IndexOf(' '); // "long SPY", "long QQQ", "short VXX"
Utils.StrongAssert(ind != -1 && ind != (bullishTradingInstrument.Length - 1), "bullishTradingInstrument parameter cannot be interpreted: " + bullishTradingInstrument);
string stock = bullishTradingInstrument.Substring(ind + 1);
string longOrShortOnBullish = bullishTradingInstrument.Substring(0, ind);
Stopwatch stopWatch = Stopwatch.StartNew();
var getAllQuotesTask = StrategiesCommon.GetHistoricalAndRealtimesQuotesAsync(p_generalParams, (new string[] { stock }).ToList());
// Control returns here before GetHistoricalQuotesAsync() returns. // ... Prompt the user.
Console.WriteLine("Please wait patiently while I do SQL and realtime price queries.");
var getAllQuotesData = await getAllQuotesTask;
stopWatch.Stop();
var stockQoutes = getAllQuotesData.Item1[0];
string noteToUserCheckData = "", noteToUserBacktest = "", debugMessage = "", errorMessage = "";
List<DailyData> pv = StrategiesCommon.DetermineBacktestPeriodCheckDataCorrectness(stockQoutes, ref noteToUserCheckData);
if (String.Equals(p_strategyName, "TotM", StringComparison.InvariantCultureIgnoreCase))
{
DoBacktestInTheTimeInterval_TotM(stockQoutes, longOrShortOnBullish, dailyMarketDirectionMaskSummerTotM, dailyMarketDirectionMaskSummerTotMM, dailyMarketDirectionMaskWinterTotM, dailyMarketDirectionMaskWinterTotMM, pv, ref noteToUserBacktest);
}
//else if (String.Equals(p_strategyName, "LETFDiscrepancy3", StringComparison.InvariantCultureIgnoreCase))
//{
// //DoBacktestInTheTimeInterval_AddToTheWinningSideWithLeverage(bullishQoutes, bearishQoutes, p_rebalancingFrequency, pv, ref noteToUserBacktest);
//}
else
{
}
stopWatchTotalResponse.Stop();
StrategyResult strategyResult = StrategiesCommon.CreateStrategyResultFromPV(pv,
//"Number of positions: <span> XXXX </span><br><br>test",
//"Number of positions: <span> {{nPositions}} </span><br><br>test",
"<b>Bullish</b> (Bearish) on days when mask is Up (Down).<br>" + noteToUserCheckData
+ ((!String.IsNullOrEmpty(noteToUserCheckData) && !String.IsNullOrEmpty(noteToUserBacktest)) ? "<br>" : "")
+ noteToUserBacktest,
errorMessage, debugMessage + String.Format("SQL query time: {0:000}ms", getAllQuotesData.Item2.TotalMilliseconds) + String.Format(", RT query time: {0:000}ms", getAllQuotesData.Item3.TotalMilliseconds) + String.Format(", All query time: {0:000}ms", stopWatch.Elapsed.TotalMilliseconds) + String.Format(", TotalC#Response: {0:000}ms", stopWatchTotalResponse.Elapsed.TotalMilliseconds));
string jsonReturn = JsonConvert.SerializeObject(strategyResult);
return jsonReturn;
}
public static async Task<string> GenerateQuickTesterResponse(GeneralStrategyParameters p_generalParams, string p_strategyName, string p_params)
{
Stopwatch stopWatchTotalResponse = Stopwatch.StartNew();
if (p_strategyName != "LETFDiscrepancy1" && p_strategyName != "LETFDiscrepancy2" && p_strategyName != "LETFDiscrepancy3")
return null;
string strategyParams = p_params;
int ind = -1;
string etfPairs = null;
if (strategyParams.StartsWith("ETFPairs=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("ETFPairs=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
return @"{ ""errorMessage"": ""Error: uriQuery.IndexOf('&') 2. Uri: " + strategyParams + @""" }";
}
etfPairs = strategyParams.Substring(0, ind);
strategyParams = strategyParams.Substring(ind + 1);
}
string rebalancingFrequency = null;
if (strategyParams.StartsWith("rebalancingFrequency=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("rebalancingFrequency=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
rebalancingFrequency = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
ind = etfPairs.IndexOf('-');
if (ind == -1)
{
return @"{ ""errorMessage"": ""Error: cannot find tickers in : " + etfPairs + @""" }";
}
string bullishTicker = etfPairs.Substring(0, ind);
string bearishTicker = etfPairs.Substring(ind +1);
// startDates
// URE: Feb 2, 2007
// SRS: Feb 1, 2007
// XIV: Nov 30, 2010
// VXX: Jan 30, 2009
// FAS: Nov 19, 2008
// FAZ: Nov 19, 2008
Stopwatch stopWatch = Stopwatch.StartNew();
var getAllQuotesTask = StrategiesCommon.GetHistoricalAndRealtimesQuotesAsync(p_generalParams, (new string[] { bullishTicker, bearishTicker }).ToList());
// Control returns here before GetHistoricalQuotesAsync() returns. // ... Prompt the user.
Console.WriteLine("Please wait patiently while I do SQL and realtime price queries.");
var getAllQuotesData = await getAllQuotesTask;
stopWatch.Stop();
string htmlNoteFromStrategy = "", noteToUserCheckData = "", noteToUserBacktest = "", debugMessage = "", errorMessage = "";
List<DailyData> pv = null;
if (String.Equals(p_strategyName, "LETFDiscrepancy1", StringComparison.InvariantCultureIgnoreCase))
{
pv = DoBacktestExample(getAllQuotesData.Item1, bullishTicker, bearishTicker, rebalancingFrequency);
}
else
{
pv = DoBacktestBasic(getAllQuotesData.Item1, bullishTicker, bearishTicker, p_strategyName, rebalancingFrequency, ref noteToUserCheckData, ref htmlNoteFromStrategy);
}
stopWatchTotalResponse.Stop();
StrategyResult strategyResult = StrategiesCommon.CreateStrategyResultFromPV(pv,
htmlNoteFromStrategy + ". " + noteToUserCheckData + "***" + noteToUserBacktest, errorMessage,
debugMessage + String.Format("SQL query time: {0:000}ms", getAllQuotesData.Item2.TotalMilliseconds) + String.Format(", RT query time: {0:000}ms", getAllQuotesData.Item3.TotalMilliseconds) + String.Format(", All query time: {0:000}ms", stopWatch.Elapsed.TotalMilliseconds) + String.Format(", TotalC#Response: {0:000}ms", stopWatchTotalResponse.Elapsed.TotalMilliseconds));
string jsonReturn = JsonConvert.SerializeObject(strategyResult);
return jsonReturn;
//{
// "Name": "Apple",
// "Expiry": "2008-12-28T00:00:00",
// "Sizes": [
// "Small"
// ]
//}
//returnStr = "[" + String.Join(Environment.NewLine,
// (await Tools.GetHistoricalQuotesAsync(new[] {
// new QuoteRequest { Ticker = "VXX", nQuotes = 2, StartDate = new DateTime(2011,1,1), NonAdjusted = true },
// new QuoteRequest { Ticker = "SPY", nQuotes = 3 }
// }, HQCommon.AssetType.Stock))
// .Select(row => String.Join(",", row))) + "]";
//returnStr = returnStr.Replace(" 00:00:00", "");
//returnStr = returnStr.Replace("\n", ",");
//return @"[{""Symbol"":""VXX""},{""Symbol"":""^VIX"",""LastUtc"":""2015-01-08T19:25:48"",""Last"":17.45,""UtcTimeType"":""LastChangedTime""}]";
}
public static async Task<string> GenerateQuickTesterResponse(GeneralStrategyParameters p_generalParams, string p_strategyName, string p_params)
{
Stopwatch stopWatchTotalResponse = Stopwatch.StartNew();
if (p_strategyName != "VXX_SPY_Controversial")
return null;
string strategyParams = p_params;
int ind = -1;
string spyMinPctMoveStr = null;
if (strategyParams.StartsWith("SpyMinPctMove=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("SpyMinPctMove=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
spyMinPctMoveStr = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
string vxxMinPctMoveStr = null;
if (strategyParams.StartsWith("VxxMinPctMove=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("VxxMinPctMove=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
vxxMinPctMoveStr = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
string longOrShortTrade = null;
if (strategyParams.StartsWith("LongOrShortTrade=", StringComparison.InvariantCultureIgnoreCase))
{
strategyParams = strategyParams.Substring("LongOrShortTrade=".Length);
ind = strategyParams.IndexOf('&');
if (ind == -1)
{
ind = strategyParams.Length;
}
longOrShortTrade = strategyParams.Substring(0, ind);
if (ind < strategyParams.Length)
strategyParams = strategyParams.Substring(ind + 1);
else
strategyParams = "";
}
double spyMinPctMove;
bool isParseSuccess = Double.TryParse(spyMinPctMoveStr, out spyMinPctMove);
if (!isParseSuccess)
{
throw new Exception("Error: spyMinPctMoveStr as " + spyMinPctMoveStr + " cannot be converted to number.");
}
double vxxMinPctMove;
isParseSuccess = Double.TryParse(vxxMinPctMoveStr, out vxxMinPctMove);
if (!isParseSuccess)
{
throw new Exception("Error: vxxMinPctMoveStr as " + vxxMinPctMoveStr + " cannot be converted to number.");
}
Stopwatch stopWatch = Stopwatch.StartNew();
var getAllQuotesTask = StrategiesCommon.GetHistoricalAndRealtimesQuotesAsync(p_generalParams, (new string[] { "VXX", "SPY" }).ToList());
// Control returns here before GetHistoricalQuotesAsync() returns. // ... Prompt the user.
Console.WriteLine("Please wait patiently while I do SQL and realtime price queries.");
var getAllQuotesData = await getAllQuotesTask;
stopWatch.Stop();
var vxxQoutes = getAllQuotesData.Item1[0];
var spyQoutes = getAllQuotesData.Item1[1];
string noteToUserCheckData = "", noteToUserBacktest = "", debugMessage = "", errorMessage = "";
List<DailyData> pv = StrategiesCommon.DetermineBacktestPeriodCheckDataCorrectness(vxxQoutes, spyQoutes, ref noteToUserCheckData);
if (String.Equals(p_strategyName, "VXX_SPY_Controversial", StringComparison.InvariantCultureIgnoreCase))
{
DoBacktestInTheTimeInterval_VXX_SPY_Controversial(vxxQoutes, spyQoutes, spyMinPctMove, vxxMinPctMove, longOrShortTrade, pv, ref noteToUserBacktest);
}
//else if (String.Equals(p_strategyName, "LETFDiscrepancy3", StringComparison.InvariantCultureIgnoreCase))
//{
// //DoBacktestInTheTimeInterval_AddToTheWinningSideWithLeverage(bullishQoutes, bearishQoutes, p_rebalancingFrequency, pv, ref noteToUserBacktest);
//}
else
{
}
stopWatchTotalResponse.Stop();
StrategyResult strategyResult = StrategiesCommon.CreateStrategyResultFromPV(pv,
noteToUserCheckData + "***" + noteToUserBacktest, errorMessage,
debugMessage + String.Format("SQL query time: {0:000}ms", getAllQuotesData.Item2.TotalMilliseconds) + String.Format(", RT query time: {0:000}ms", getAllQuotesData.Item3.TotalMilliseconds) + String.Format(", All query time: {0:000}ms", stopWatch.Elapsed.TotalMilliseconds) + String.Format(", TotalC#Response: {0:000}ms", stopWatchTotalResponse.Elapsed.TotalMilliseconds));
string jsonReturn = JsonConvert.SerializeObject(strategyResult);
return jsonReturn;
}
public static async Task <Tuple <IList <List <DailyData> >, TimeSpan, TimeSpan> > GetHistoricalAndRealtimesQuotesAsync(GeneralStrategyParameters p_generalParams, List <string> p_tickers, CancellationToken p_canc = default(CancellationToken))
{
//- SPY 300K CSV SQLqueryTime (local server), msecond times for (for Azure in-house datacenter, these will be less)
//All data: Open, High, Low, Close, Volume : 886, 706, 1237, 761, 727, Avg = 863
//Only ClosePrice: 662, 680, 702, 820, 663, 692, Avg = 703
// if the Website is not local, but it is in the same Azure datacenter as the SQL center
//SQL query time (All OHLCV data): msec: 612, 614, 667, 772, 632, 613, 665, 662, Avg = 654
//SQL query time (only Close data): msec: 623, 624, 704, 614, 615, 621, 621, 722, 636, Avg = 642
//Conclusion:downloading only Closeprice from SQL, we can save 100msec (LocalServer website (non-datacenter website)) or 15msec (Azure server website when SQL server is very close), still worth it
ushort sqlReturnedColumns = QuoteRequest.TDC; // QuoteRequest.All or QuoteRequest.TDOHLCVS
//var sqlReturnTask = GetHistQuotesAsync(p_tickers.Select(r => new QuoteRequest { Ticker = r, nQuotes = Int32.MaxValue, NonAdjusted = false, ReturnedColumns = sqlReturnedColumns }), HQCommon.AssetType.Stock, true); // Ascending date order: TRUE, better to order it at the SQL server than locally. SQL has indexers
var sqlReturnTask = GetHistQuotesAsync(p_generalParams, p_tickers, sqlReturnedColumns);
Task <Tuple <IList <double?>, TimeSpan> > realtimeReturnTask = null;
if (p_generalParams.endDateUtc >= DateTime.UtcNow)
{
realtimeReturnTask = GetRealtimesQuotesAsync(p_tickers);
}
// Control returns here before GetHistoricalQuotesAsync() returns. // ... Prompt the user.
Console.WriteLine("Please wait patiently while I do SQL and realtime price queries.");
// Wait for the tasks to complete. // ... Display its results.
//var combinedAsyncTasksResults = await Task.WhenAll(sqlReturnTask, realtimeReturnTask); this cannot be done now, because the return values are different
if (realtimeReturnTask != null)
{
await Task.WhenAll(sqlReturnTask, realtimeReturnTask); // otherwise, the next await will wait the historical data
}
var sqlReturnData = await sqlReturnTask; //as they have all definitely finished, you could also use Task.Value, "However, I recommend using await because it's clearly correct, while Result can cause problems in other scenarios."
Tuple <IList <double?>, TimeSpan> realtimeReturnData = null;
if (realtimeReturnTask != null)
{
realtimeReturnData = await realtimeReturnTask; //as they have all definitely finished, you could also use Task.Value, "However, I recommend using await because it's clearly correct, while Result can cause problems in other scenarios."
}
var sqlReturn = sqlReturnData.Item1;
List <List <DailyData> > returnQuotes = null;
// sql query of "VXX.SQ" gives back tickers of VXX and also tickers of "VXX.SQ"
int closePriceIndex = -1;
if (sqlReturnedColumns == QuoteRequest.TDOHLCVS)
{
closePriceIndex = 5;
}
else if (sqlReturnedColumns == QuoteRequest.TDC)
{
closePriceIndex = 2;
}
else
{
throw new NotImplementedException();
}
returnQuotes = p_tickers.Select(ticker =>
{
string tickerWithoutDotSQ = "";
if (ticker.EndsWith(".SQ"))
{
tickerWithoutDotSQ = ticker.Substring(0, ticker.Length - ".SQ".Length);
}
return(sqlReturn.Where(row => (string)row[0] == ticker || (string)row[0] == tickerWithoutDotSQ).Select(
row => new DailyData()
{
Date = ((DateTime)row[1]),
ClosePrice = (double)Convert.ToDecimal(row[closePriceIndex]) // row[2] is object(double) if it is a stock (because Adjustment multiplier), and object(float) if it is Indices. However Convert.ToDouble(row[2]) would convert 16.66 to 16.6599999
}).ToList());
}).ToList();
if (realtimeReturnData != null)
{
var todayDate = DateTime.UtcNow.Date;
var realtimeReturn = realtimeReturnData.Item1;
for (int i = 0; i < p_tickers.Count(); i++)
{
if (realtimeReturn[i] != null)
{
int todayInd = returnQuotes[i].FindLastIndex(r => r.Date == todayDate);
if (todayInd == -1) // if it is missing
{
returnQuotes[i].Add(new DailyData()
{
Date = todayDate, ClosePrice = (double)realtimeReturn[i]
});
}
else // if it is already in the array, overwrite it
{
returnQuotes[i][todayInd].ClosePrice = (double)realtimeReturn[i];
}
}
}
}
return(new Tuple <IList <List <DailyData> >, TimeSpan, TimeSpan>(returnQuotes, sqlReturnData.Item2, (realtimeReturnData != null)?realtimeReturnData.Item2:TimeSpan.Zero));
}
//public static async Task<Tuple<IList<object[]>,TimeSpan>> GetHistQuotesAsync(IEnumerable<QuoteRequest> p_req, HQCommon.AssetType p_at, bool? p_isAscendingDates = null, CancellationToken p_canc = default(CancellationToken))
public static async Task <Tuple <List <object[]>, TimeSpan> > GetHistQuotesAsync(GeneralStrategyParameters p_generalParams, List <string> p_tickers, ushort p_sqlReturnedColumns)
{
List <string> stockTickers = p_tickers.Where(r => !r.StartsWith("^")).ToList();
List <string> indicesTickers = p_tickers.Where(r => r.StartsWith("^")).ToList();
TimeZoneInfo etZone = null;
int requestNQuotes = Int32.MaxValue;
DateTime?requestStartDateExcgLocal = null, requestEndDateExcgLocal = null;
if (p_generalParams.startDateUtc != DateTime.MinValue)
{
ConvertUtcToExchangeLocal(p_generalParams.startDateUtc, ref etZone, ref requestStartDateExcgLocal);
}
if (p_generalParams.endDateUtc != DateTime.MaxValue)
{
ConvertUtcToExchangeLocal(p_generalParams.endDateUtc, ref etZone, ref requestEndDateExcgLocal);
}
Stopwatch stopWatch = Stopwatch.StartNew();
Task <IList <object[]> > stocksSqlReturnTask = null, indicesSqlReturnTask = null;;
IList <object[]> stocksSqlReturn = null, indicesSqlReturn = null;
if (stockTickers.Count != 0)
{
stocksSqlReturnTask = Tools.GetHistoricalQuotesAsync(stockTickers.Select(r => new QuoteRequest {
Ticker = r, nQuotes = requestNQuotes, StartDate = requestStartDateExcgLocal, EndDate = requestEndDateExcgLocal, NonAdjusted = false, ReturnedColumns = p_sqlReturnedColumns
}), HQCommon.AssetType.Stock, true); // Ascending date order: TRUE, better to order it at the SQL server than locally. SQL has indexers
}
if (indicesTickers.Count != 0)
{
indicesSqlReturnTask = Tools.GetHistoricalQuotesAsync(indicesTickers.Select(r => new QuoteRequest {
Ticker = r, nQuotes = requestNQuotes, StartDate = requestStartDateExcgLocal, EndDate = requestEndDateExcgLocal, NonAdjusted = false, ReturnedColumns = p_sqlReturnedColumns
}), HQCommon.AssetType.BenchmarkIndex, true); // Ascending date order: TRUE, better to order it at the SQL server than locally. SQL has indexers
}
if (stockTickers.Count != 0)
{
stocksSqlReturn = await stocksSqlReturnTask;
}
if (indicesTickers.Count != 0)
{
indicesSqlReturn = await indicesSqlReturnTask;
}
stopWatch.Stop();
TimeSpan historicalQueryTimeSpan = stopWatch.Elapsed;
List <object[]> sqlReturn = null;
if (stocksSqlReturn != null)
{
sqlReturn = stocksSqlReturn.ToList(); // the return is a List() already if you look deeper in the implementation
}
if (indicesSqlReturn != null)
{
if (sqlReturn == null)
{
sqlReturn = indicesSqlReturn.ToList();
}
else
{
sqlReturn.AddRange(indicesSqlReturn.ToList());
}
}
return(new Tuple <List <object[]>, TimeSpan>(sqlReturn, historicalQueryTimeSpan));
}
