[Quantproject-developers] QuantProject/b2_DataAccess/Tables Quotes.cs,1.13,1.14 Tickers_tickerGroups
Brought to you by:
glauco_1
Menu
▾
▴
[Quantproject-developers] QuantProject/b2_DataAccess/Tables Quotes.cs,1.13,1.14 Tickers_tickerGroups.cs,1.2,1.3
|
From: Marco M. <mi...@us...> - 2004-07-25 12:09:03
|
Update of /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv4857/b2_DataAccess/Tables Modified Files: Quotes.cs Tickers_tickerGroups.cs Log Message: New methods to: - ExtendedDataTable class; - Quotes and Tickers_tickerGroups in DataAccess layer; - Quotes and TickerDataTable in Data layer Index: Quotes.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/Quotes.cs,v retrieving revision 1.13 retrieving revision 1.14 diff -C2 -d -r1.13 -r1.14 *** Quotes.cs 27 Jun 2004 19:16:15 -0000 1.13 --- Quotes.cs 25 Jul 2004 12:08:53 -0000 1.14 *************** *** 93,96 **** --- 93,97 ---- /// <summary> /// Returns the adjusted close value for the given ticker at the specified date + /// is returned /// </summary> /// <param name="ticker">ticker for which the adj close has to be returned</param> *************** *** 103,107 **** --- 104,144 ---- return (float)dataTable.Rows[0][0]; } + /* moved now to the quotes object in the data layer, where the names are slightly different + + /// <summary> + /// Returns true if a quote is available for the given ticker at the given date + /// </summary> + /// <param name="ticker">ticker for which the check has to be done</param> + /// <param name="date">date of the check</param> + /// <returns></returns> + public static bool IsQuoteAvailable( string ticker, DateTime date ) + { + DataTable dataTable = SqlExecutor.GetDataTable( + "select quAdjustedClose from quotes where quTicker='" + ticker + "' " + + "and quDate=" + SQLBuilder.GetDateConstant(date) ); + string booleanStringValue = "false"; + if(dataTable.Rows.Count>0) + booleanStringValue = "True"; + return Boolean.Parse(booleanStringValue); + } + public static DateTime GetFollowingValidQuoteDate( string ticker, DateTime date ) + { + if(Quotes.IsQuoteAvailable(ticker, date)) + { + return date; + } + else return GetFollowingValidQuoteDate(ticker, date.AddDays(1)); + } + + public static DateTime GetPrecedingValidQuoteDate( string ticker, DateTime date ) + { + if(Quotes.IsQuoteAvailable(ticker, date)) + { + return date; + } + else return GetPrecedingValidQuoteDate(ticker, date.Subtract(new TimeSpan(1,0,0))); + } + */ /// <summary> /// It provides updating the database for each closeToCloseRatio contained in the given table *************** *** 352,356 **** /// </summary> ! public static DataTable GetMostLiquidTickers( string groupID, DateTime firstQuoteDate, DateTime lastQuoteDate, --- 389,393 ---- /// </summary> ! public static DataTable GetTickersByLiquidity( bool orderInASCMode, string groupID, DateTime firstQuoteDate, DateTime lastQuoteDate, *************** *** 367,409 **** SQLBuilder.GetDateConstant(lastQuoteDate) + "GROUP BY tickers.tiTicker, tickers.tiCompanyName " + ! "ORDER BY Avg([quVolume]*[quAdjustedClose]) DESC"; ! return SqlExecutor.GetDataTable( sql ); } /// <summary> ! /// returns most liquid tickers within the given set of tickers /// </summary> ! ! public static DataTable GetMostLiquidTickers( DataTable setOfTickers, ! DateTime firstQuoteDate, ! DateTime lastQuoteDate, ! long maxNumOfReturnedTickers) { - setOfTickers.Columns.Add("IndexOfLiquidity", System.Type.GetType("System.Double")); - DataTable getMostLiquidTicker = setOfTickers.Clone(); DataTable dt; ! foreach(DataRow row in setOfTickers.Rows) ! { ! string sql = "SELECT quotes.quTicker, " + ! "Avg([quVolume]*[quAdjustedClose]) AS AverageTradedValue " + ! "FROM quotes WHERE quTicker ='" + ! (string)row[0] + "' " + ! "AND quotes.quDate BETWEEN " + SQLBuilder.GetDateConstant(firstQuoteDate) + ! " AND " + SQLBuilder.GetDateConstant(lastQuoteDate) + ! " GROUP BY quotes.quTicker"; ! dt = SqlExecutor.GetDataTable( sql ); ! row["IndexOfLiquidity"] = (double)dt.Rows[0]["AverageTradedValue"]; ! } ! DataRow[] orderedRows = setOfTickers.Select("", "IndexOfLiquidity DESC"); ! object[] valuesToAdd = new object[3]; ! for(long i = 0;i<maxNumOfReturnedTickers && i<setOfTickers.Rows.Count;i++) ! { ! valuesToAdd[0]=orderedRows[i][0]; ! valuesToAdd[1]=orderedRows[i][1]; ! valuesToAdd[2]=orderedRows[i][2]; ! getMostLiquidTicker.Rows.Add(valuesToAdd); ! } ! return getMostLiquidTicker; ! } #region GetHashValue --- 404,437 ---- SQLBuilder.GetDateConstant(lastQuoteDate) + "GROUP BY tickers.tiTicker, tickers.tiCompanyName " + ! "ORDER BY Avg([quVolume]*[quAdjustedClose])"; ! string sortDirection = " DESC"; ! if(orderInASCMode) ! sortDirection = " ASC"; ! sql = sql + sortDirection; return SqlExecutor.GetDataTable( sql ); } + /// <summary> ! /// returns the average traded value for the given ticker in the specified interval /// </summary> ! public static double GetAverageTradedValue( string ticker, ! DateTime firstQuoteDate, ! DateTime lastQuoteDate) ! { DataTable dt; ! string sql = "SELECT quotes.quTicker, " + ! "Avg([quVolume]*[quAdjustedClose]) AS AverageTradedValue " + ! "FROM quotes WHERE quTicker ='" + ! ticker + "' " + ! "AND quotes.quDate BETWEEN " + SQLBuilder.GetDateConstant(firstQuoteDate) + ! " AND " + SQLBuilder.GetDateConstant(lastQuoteDate) + ! " GROUP BY quotes.quTicker"; ! dt = SqlExecutor.GetDataTable( sql ); ! if(dt.Rows.Count==0) ! return 0; ! else ! return (double)dt.Rows[0]["AverageTradedValue"]; ! } #region GetHashValue *************** *** 501,518 **** /// Returns the quotes for the given instrument , since startDate to endDate /// </summary> ! /// <param name="ticker"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> ! public static void SetDataTable( string ticker , DateTime startDate , DateTime endDate , DataTable dataTable) { ! string sql = ! "select * from quotes " + ! "where " + Quotes.TickerFieldName + "='" + ticker + "' " + ! "and " + Quotes.Date + ">=" + SQLBuilder.GetDateConstant( startDate ) + " " + ! "and " + Quotes.Date + "<=" + SQLBuilder.GetDateConstant( endDate ) + " " + ! "order by " + Quotes.Date; ! SqlExecutor.SetDataTable( sql , dataTable ); } --- 529,555 ---- /// Returns the quotes for the given instrument , since startDate to endDate /// </summary> ! /// <param name="tickerOrGroupID">The symbol of a ticker or the groupID corresponding to a specific set of tickers</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> ! public static void SetDataTable( string tickerOrGroupID , DateTime startDate , DateTime endDate , DataTable dataTable) { ! string sql; ! if(Tickers_tickerGroups.HasTickers(tickerOrGroupID)) ! sql = "select * from quotes INNER JOIN tickers_tickerGroups ON " + ! "quotes." + Quotes.TickerFieldName + "=tickers_tickerGroups." + Tickers_tickerGroups.Ticker + " " + ! "where " + Tickers_tickerGroups.GroupID + "='" + tickerOrGroupID + "' " + ! "and " + Quotes.Date + ">=" + SQLBuilder.GetDateConstant( startDate ) + " " + ! "and " + Quotes.Date + "<=" + SQLBuilder.GetDateConstant( endDate ) + " " + ! "order by " + Quotes.Date; ! else ! sql = "select * from quotes " + ! "where " + Quotes.TickerFieldName + "='" + tickerOrGroupID + "' " + ! "and " + Quotes.Date + ">=" + SQLBuilder.GetDateConstant( startDate ) + " " + ! "and " + Quotes.Date + "<=" + SQLBuilder.GetDateConstant( endDate ) + " " + ! "order by " + Quotes.Date; ! ! SqlExecutor.SetDataTable( sql , dataTable ); } Index: Tickers_tickerGroups.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/Tickers_tickerGroups.cs,v retrieving revision 1.2 retrieving revision 1.3 diff -C2 -d -r1.2 -r1.3 *** Tickers_tickerGroups.cs 27 Jun 2004 19:20:30 -0000 1.2 --- Tickers_tickerGroups.cs 25 Jul 2004 12:08:53 -0000 1.3 *************** *** 92,95 **** --- 92,107 ---- } + /// <summary> + /// It returns true if some tickers are grouped in the given groupID + /// </summary> + public static bool HasTickers( string groupID) + { + /// TO DO use a join in order to return a table with tiTicker and company name + DataTable tickers = SqlExecutor.GetDataTable("SELECT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + + "WHERE " + Tickers_tickerGroups.GroupID + "='" + + groupID + "'"); + return tickers.Rows.Count > 0; + } + } |
