436 lines
30 KiB
C#
436 lines
30 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class ValuationDA
|
|
{
|
|
private ValuationDA()
|
|
{
|
|
}
|
|
public static List<DateTime> GetValuationDates()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
List<DateTime> valuationDates = new List<DateTime>();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select distinct(valuation_date) from valuations order by valuation_date desc");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
valuationDates.Add(sqlDataReader.GetDateTime(0));
|
|
}
|
|
return valuationDates;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static DateTime GetLatestValuationDate()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
DateTime valuationDate=Utility.Epoch;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select max(valuation_date) from valuations");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (sqlDataReader.Read()) valuationDate = sqlDataReader.GetDateTime(0);
|
|
return valuationDate;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return valuationDate;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static Valuations GetValuation(DateTime valuationDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
Valuations valuations = new Valuations();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (Utility.IsEpoch(valuationDate)) return null;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("select valuation_date,symbol,company,next_earnings_date,long_term_debt,revenue,debt_load,roic_avg,bvps_dates,avg_equity_growth,avg_equity_growth_2y,avg_equity_growth_4y,eps_dates,avg_eps_growth,avg_eps_growth_2y,avg_eps_growth_4y,revenue_dates,avg_revenue_growth,avg_revenue_growth_2y,avg_revenue_growth_4y,avg_freecashflow_growth,avg_operating_cashflow,avg_working_capital,bvps,eps,pe,low_pe,trailing_pe,avg_low_trailing,current_stock_estimate_price,price_estimate_10y,todays_price_for_required_return,mos,mos_80,intrinsic_value,rgv,latest_price,upside_pcnt,downside_pcnt,mean_target_price,low_target_price,high_target_price,bargain_mos,bargain_mos_80,price_to_book,peg,implied_earnings_growth,beta90,beta2y,roic_slope,roic_dates,market_cap,earnings_yield,ebit,enterprise_value,tlb_rank_roic,fundamental_value,net_current_asset_value_per_share,roic_latest,sector,industry,debt_to_equity,sec_13,sec_13_filing_date,roc_latest,tlb_rank_roc,operating_earnings,am_rank,acquirers_multiple,dividend_yield,operating_cashflow,shares_outstanding,modified").Append(" ");
|
|
sb.Append("from valuations where valuation_date=").Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuationDate)));
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
valuations.ValuationDate = valuationDate;
|
|
while (sqlDataReader.Read())
|
|
{
|
|
Valuation valuation = new Valuation();
|
|
valuation.Symbol = sqlDataReader.GetString(1);
|
|
valuation.Company = sqlDataReader.GetString(2);
|
|
if (!sqlDataReader.IsDBNull(3)) valuation.NextEarningsDate = sqlDataReader.GetDateTime(3);
|
|
else valuation.NextEarningsDate = Utility.Epoch;
|
|
if (!sqlDataReader.IsDBNull(4)) valuation.LongTermDebt = sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5)) valuation.Revenue = sqlDataReader.GetDouble(5);
|
|
valuation.DebtLoad = sqlDataReader.GetString(6);
|
|
if (!sqlDataReader.IsDBNull(7)) valuation.AverageROIC = sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8)) valuation.BVPSDates = sqlDataReader.GetString(8);
|
|
if (!sqlDataReader.IsDBNull(9)) valuation.AverageEquityGrowth = sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10)) valuation.AverageEquityGrowth2Y = sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) valuation.AverageEquityGrowth4Y = sqlDataReader.GetDouble(11);
|
|
valuation.EPSDates = sqlDataReader.GetString(12);
|
|
if (!sqlDataReader.IsDBNull(13)) valuation.AverageEPSGrowth = sqlDataReader.GetDouble(13);
|
|
if (!sqlDataReader.IsDBNull(14)) valuation.AverageEPSGrowth2Y = sqlDataReader.GetDouble(14);
|
|
if (!sqlDataReader.IsDBNull(15)) valuation.AverageEPSGrowth4Y = sqlDataReader.GetDouble(15);
|
|
valuation.RevenueDates = sqlDataReader.GetString(16);
|
|
if (!sqlDataReader.IsDBNull(17)) valuation.AverageRevenueGrowth = sqlDataReader.GetDouble(17);
|
|
if (!sqlDataReader.IsDBNull(18)) valuation.AverageRevenueGrowth2Y = sqlDataReader.GetDouble(18);
|
|
if (!sqlDataReader.IsDBNull(19)) valuation.AverageRevenueGrowth4Y = sqlDataReader.GetDouble(19);
|
|
if (!sqlDataReader.IsDBNull(20)) valuation.AverageFreeCashflowGrowth = sqlDataReader.GetDouble(20);
|
|
if (!sqlDataReader.IsDBNull(21)) valuation.AverageOperatingCashflow = sqlDataReader.GetDouble(21);
|
|
if (!sqlDataReader.IsDBNull(22)) valuation.AverageWorkingCapital = sqlDataReader.GetDouble(22);
|
|
if (!sqlDataReader.IsDBNull(23)) valuation.BVPS = sqlDataReader.GetDouble(23);
|
|
if (!sqlDataReader.IsDBNull(24)) valuation.EPS = sqlDataReader.GetDouble(24);
|
|
if (!sqlDataReader.IsDBNull(25)) valuation.PE = sqlDataReader.GetDouble(25);
|
|
if (!sqlDataReader.IsDBNull(26)) valuation.LowPE = sqlDataReader.GetDouble(26);
|
|
if (!sqlDataReader.IsDBNull(27)) valuation.TrailingPE = sqlDataReader.GetDouble(27);
|
|
if (!sqlDataReader.IsDBNull(28)) valuation.AverageLowTrailing = sqlDataReader.GetDouble(28);
|
|
if (!sqlDataReader.IsDBNull(29)) valuation.CurrentStockEstimatePrice = sqlDataReader.GetDouble(29);
|
|
if (!sqlDataReader.IsDBNull(30)) valuation.PriceEstimate10Y = sqlDataReader.GetDouble(30);
|
|
if (!sqlDataReader.IsDBNull(31)) valuation.TodaysPriceForRequiredReturn = sqlDataReader.GetDouble(31);
|
|
if (!sqlDataReader.IsDBNull(32)) valuation.MOS = sqlDataReader.GetDouble(32);
|
|
if (!sqlDataReader.IsDBNull(33)) valuation.MOS80 = sqlDataReader.GetDouble(33);
|
|
if (!sqlDataReader.IsDBNull(34)) valuation.IntrinsicValue = sqlDataReader.GetDouble(34);
|
|
if (!sqlDataReader.IsDBNull(35)) valuation.RGV = sqlDataReader.GetDouble(35);
|
|
if (!sqlDataReader.IsDBNull(36)) valuation.LatestPrice = sqlDataReader.GetDouble(36);
|
|
if (!sqlDataReader.IsDBNull(37)) valuation.UpsidePcnt = sqlDataReader.GetDouble(37);
|
|
if (!sqlDataReader.IsDBNull(38)) valuation.DownsidePcnt = sqlDataReader.GetDouble(38);
|
|
if (!sqlDataReader.IsDBNull(39)) valuation.MeanTargetPrice = sqlDataReader.GetDouble(39);
|
|
if (!sqlDataReader.IsDBNull(40)) valuation.LowTargetPrice = sqlDataReader.GetDouble(40);
|
|
if (!sqlDataReader.IsDBNull(41)) valuation.HighTargetPrice = sqlDataReader.GetDouble(41);
|
|
valuation.Bargain = sqlDataReader.GetBoolean(42);
|
|
valuation.Bargain80 = sqlDataReader.GetBoolean(43);
|
|
if (!sqlDataReader.IsDBNull(44)) valuation.PBVPS = sqlDataReader.GetDouble(44);
|
|
if (!sqlDataReader.IsDBNull(45)) valuation.PEG = sqlDataReader.GetDouble(45);
|
|
if (!sqlDataReader.IsDBNull(46)) valuation.ImpliedEarningsGrowth = sqlDataReader.GetDouble(46);
|
|
if (!sqlDataReader.IsDBNull(47)) valuation.Beta90 = sqlDataReader.GetDouble(47);
|
|
if (!sqlDataReader.IsDBNull(48)) valuation.Beta720 = sqlDataReader.GetDouble(48);
|
|
if (!sqlDataReader.IsDBNull(49)) valuation.ROICSlope = sqlDataReader.GetDouble(49);
|
|
if (!sqlDataReader.IsDBNull(50)) valuation.ROICDates = sqlDataReader.GetString(50);
|
|
if (!sqlDataReader.IsDBNull(51)) valuation.MarketCap = sqlDataReader.GetDouble(51);
|
|
if (!sqlDataReader.IsDBNull(52)) valuation.EarningsYield = sqlDataReader.GetDouble(52);
|
|
if (!sqlDataReader.IsDBNull(53)) valuation.EBIT = sqlDataReader.GetDouble(53);
|
|
if (!sqlDataReader.IsDBNull(54)) valuation.EnterpriseValue = sqlDataReader.GetDouble(54);
|
|
if (!sqlDataReader.IsDBNull(55)) valuation.TLBRankROIC = sqlDataReader.GetDouble(55);
|
|
if (!sqlDataReader.IsDBNull(56)) valuation.FundamentalValue = sqlDataReader.GetDouble(56);
|
|
if (!sqlDataReader.IsDBNull(57)) valuation.NetCurrentAssetValuePerShare = sqlDataReader.GetDouble(57);
|
|
if (!sqlDataReader.IsDBNull(58)) valuation.LatestROIC = sqlDataReader.GetDouble(58);
|
|
if (!sqlDataReader.IsDBNull(59)) valuation.Sector=sqlDataReader.GetString(59);
|
|
if (!sqlDataReader.IsDBNull(60)) valuation.Industry = sqlDataReader.GetString(60);
|
|
if (!sqlDataReader.IsDBNull(61)) valuation.DebtToEquity = sqlDataReader.GetDouble(61);
|
|
if (!sqlDataReader.IsDBNull(62)) valuation.SEC13 = sqlDataReader.GetBoolean(62);
|
|
if (!sqlDataReader.IsDBNull(63)) valuation.SEC13FilingDate = sqlDataReader.GetDateTime(63);
|
|
if (!sqlDataReader.IsDBNull(64)) valuation.LatestROC = sqlDataReader.GetDouble(64);
|
|
if (!sqlDataReader.IsDBNull(65)) valuation.TLBRankROC = sqlDataReader.GetDouble(65);
|
|
if (!sqlDataReader.IsDBNull(66)) valuation.OperatingEarnings = sqlDataReader.GetDouble(66);
|
|
if (!sqlDataReader.IsDBNull(67)) valuation.AMRank = sqlDataReader.GetDouble(67);
|
|
if (!sqlDataReader.IsDBNull(68)) valuation.AcquirersMultiple = sqlDataReader.GetDouble(68);
|
|
if (!sqlDataReader.IsDBNull(69)) valuation.DividendYield = sqlDataReader.GetDouble(69);
|
|
if (!sqlDataReader.IsDBNull(70)) valuation.OperatingCashflow = sqlDataReader.GetDouble(70);
|
|
if (!sqlDataReader.IsDBNull(71)) valuation.SharesOutstanding = sqlDataReader.GetDouble(71);
|
|
valuation.Modified = sqlDataReader.GetDateTime(72);
|
|
valuations.Add(valuation);
|
|
}
|
|
return valuations;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static bool AddValuations(Valuations valuations)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
String strQuery = null;
|
|
DateTime modified = DateTime.Now;
|
|
|
|
try
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
RemoveValuation(valuations.ValuationDate,sqlTransaction);
|
|
foreach(Valuation valuation in valuations)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into valuations(valuation_date,symbol,company,next_earnings_date,long_term_debt,revenue,debt_load,roic_avg,roic_slope,roic_dates,bvps_dates,avg_equity_growth,avg_equity_growth_2y,avg_equity_growth_4y,eps_dates,avg_eps_growth,avg_eps_growth_2y,avg_eps_growth_4y,revenue_dates,avg_revenue_growth,avg_revenue_growth_2y,avg_revenue_growth_4y,avg_freecashflow_growth,avg_operating_cashflow,avg_working_capital,bvps,eps,pe,low_pe,trailing_pe,avg_low_trailing,current_stock_estimate_price,price_estimate_10y,todays_price_for_required_return,mos,mos_80,intrinsic_value,rgv,latest_price,upside_pcnt,downside_pcnt,mean_target_price,low_target_price,high_target_price,bargain_mos,bargain_mos_80,price_to_book,peg,implied_earnings_growth,beta90,beta2y,market_cap,earnings_yield,ebit,enterprise_value,tlb_rank_roic,fundamental_value,net_current_asset_value_per_share,roic_latest,sector,industry,debt_to_equity,sec_13,sec_13_filing_date,roc_latest,tlb_rank_roc,operating_earnings,am_rank,acquirers_multiple,dividend_yield,operating_cashflow,shares_outstanding,modified)values(");
|
|
sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuations.ValuationDate))).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(valuation.Symbol)).Append(",");
|
|
if (null == valuation.Company) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlString(valuation.Company))).Append(",");
|
|
if (Utility.IsEpoch(valuation.NextEarningsDate)) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuation.NextEarningsDate))).Append(",");
|
|
if (double.IsNaN(valuation.LongTermDebt) || double.IsInfinity(valuation.LongTermDebt)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.LongTermDebt).Append(",");
|
|
if (double.IsNaN(valuation.Revenue) || double.IsInfinity(valuation.Revenue)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.Revenue).Append(",");
|
|
if (null == valuation.DebtLoad) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.DebtLoad)).Append(",");
|
|
if (double.IsNaN(valuation.AverageROIC) || double.IsInfinity(valuation.AverageROIC)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageROIC).Append(",");
|
|
if (double.IsNaN(valuation.ROICSlope) || double.IsInfinity(valuation.ROICSlope)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.ROICSlope).Append(",");
|
|
if (null == valuation.ROICDates) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.ROICDates)).Append(",");
|
|
if (null == valuation.BVPSDates) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.BVPSDates)).Append(",");
|
|
if (double.IsNaN(valuation.AverageEquityGrowth) || double.IsInfinity(valuation.AverageEquityGrowth)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageEquityGrowth).Append(",");
|
|
if (double.IsNaN(valuation.AverageEquityGrowth2Y) || double.IsInfinity(valuation.AverageEquityGrowth2Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageEquityGrowth2Y).Append(",");
|
|
if (double.IsNaN(valuation.AverageEquityGrowth4Y) || double.IsInfinity(valuation.AverageEquityGrowth4Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageEquityGrowth4Y).Append(",");
|
|
if (null == valuation.EPSDates) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.EPSDates)).Append(",");
|
|
if (double.IsNaN(valuation.AverageEPSGrowth) || double.IsInfinity(valuation.AverageEPSGrowth)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageEPSGrowth).Append(",");
|
|
if (double.IsNaN(valuation.AverageEPSGrowth2Y) || double.IsInfinity(valuation.AverageEPSGrowth2Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageEPSGrowth2Y).Append(",");
|
|
if (double.IsNaN(valuation.AverageEPSGrowth4Y) || double.IsInfinity(valuation.AverageEPSGrowth4Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageEPSGrowth4Y).Append(",");
|
|
if (null == valuation.RevenueDates) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.RevenueDates)).Append(",");
|
|
if (double.IsNaN(valuation.AverageRevenueGrowth) || double.IsInfinity(valuation.AverageRevenueGrowth)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageRevenueGrowth).Append(",");
|
|
if (double.IsNaN(valuation.AverageRevenueGrowth2Y) || double.IsInfinity(valuation.AverageRevenueGrowth2Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageRevenueGrowth2Y).Append(",");
|
|
if (double.IsNaN(valuation.AverageRevenueGrowth4Y) || double.IsInfinity(valuation.AverageRevenueGrowth4Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageRevenueGrowth4Y).Append(",");
|
|
if (double.IsNaN(valuation.AverageFreeCashflowGrowth) || double.IsInfinity(valuation.AverageFreeCashflowGrowth)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageFreeCashflowGrowth).Append(",");
|
|
if (double.IsNaN(valuation.AverageOperatingCashflow) || double.IsInfinity(valuation.AverageOperatingCashflow)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageOperatingCashflow).Append(",");
|
|
if (double.IsNaN(valuation.AverageWorkingCapital) || double.IsInfinity(valuation.AverageWorkingCapital)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageWorkingCapital).Append(",");
|
|
if (double.IsNaN(valuation.BVPS) || double.IsInfinity(valuation.BVPS)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.BVPS).Append(",");
|
|
if (double.IsNaN(valuation.EPS) || double.IsInfinity(valuation.EPS)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.EPS).Append(",");
|
|
if (double.IsNaN(valuation.PE) || double.IsInfinity(valuation.PE)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.PE).Append(",");
|
|
if (double.IsNaN(valuation.LowPE) || double.IsInfinity(valuation.LowPE)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.LowPE).Append(",");
|
|
if (double.IsNaN(valuation.TrailingPE) || double.IsInfinity(valuation.TrailingPE)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.TrailingPE).Append(",");
|
|
if (double.IsNaN(valuation.AverageLowTrailing) || double.IsInfinity(valuation.AverageLowTrailing)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AverageLowTrailing).Append(",");
|
|
if (double.IsNaN(valuation.CurrentStockEstimatePrice) || double.IsInfinity(valuation.CurrentStockEstimatePrice)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.CurrentStockEstimatePrice).Append(",");
|
|
if (valuation.PriceEstimate10Y<0 || double.IsNaN(valuation.PriceEstimate10Y) || double.IsInfinity(valuation.PriceEstimate10Y) || Utility.OutOfRange(valuation.PriceEstimate10Y)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.PriceEstimate10Y).Append(",");
|
|
if (double.IsNaN(valuation.TodaysPriceForRequiredReturn) || double.IsInfinity(valuation.TodaysPriceForRequiredReturn) || Utility.OutOfRange(valuation.TodaysPriceForRequiredReturn)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.TodaysPriceForRequiredReturn).Append(",");
|
|
if (double.IsNaN(valuation.MOS) || double.IsInfinity(valuation.MOS) || Utility.OutOfRange(valuation.MOS)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.MOS).Append(",");
|
|
if (double.IsNaN(valuation.MOS80) || double.IsInfinity(valuation.MOS80) || Utility.OutOfRange(valuation.MOS80)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.MOS80).Append(",");
|
|
if (double.IsNaN(valuation.IntrinsicValue) || double.IsInfinity(valuation.IntrinsicValue) || Utility.OutOfRange(valuation.IntrinsicValue)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.IntrinsicValue).Append(",");
|
|
if (double.IsNaN(valuation.RGV) || double.IsInfinity(valuation.RGV) || Utility.OutOfRange(valuation.RGV)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.RGV).Append(",");
|
|
if (double.IsNaN(valuation.LatestPrice) || double.IsInfinity(valuation.LatestPrice)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.LatestPrice).Append(",");
|
|
if (double.IsNaN(valuation.UpsidePcnt) || double.IsInfinity(valuation.UpsidePcnt)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.UpsidePcnt).Append(",");
|
|
if (double.IsNaN(valuation.DownsidePcnt) || double.IsInfinity(valuation.DownsidePcnt)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.DownsidePcnt).Append(",");
|
|
if (double.IsNaN(valuation.MeanTargetPrice) || double.IsInfinity(valuation.MeanTargetPrice)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.MeanTargetPrice).Append(",");
|
|
if (double.IsNaN(valuation.LowTargetPrice) || double.IsInfinity(valuation.LowTargetPrice)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.LowTargetPrice).Append(",");
|
|
if (double.IsNaN(valuation.HighTargetPrice) || double.IsInfinity(valuation.HighTargetPrice)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.HighTargetPrice).Append(",");
|
|
sb.Append(valuation.Bargain).Append(",");
|
|
sb.Append(valuation.Bargain80).Append(",");
|
|
if (double.IsNaN(valuation.PBVPS) || double.IsInfinity(valuation.PBVPS)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.PBVPS).Append(",");
|
|
if (double.IsNaN(valuation.PEG) || double.IsInfinity(valuation.PEG)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.PEG).Append(",");
|
|
if (double.IsNaN(valuation.ImpliedEarningsGrowth) || double.IsInfinity(valuation.ImpliedEarningsGrowth)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.ImpliedEarningsGrowth).Append(",");
|
|
if (double.IsNaN(valuation.Beta90) || double.IsInfinity(valuation.Beta90)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.Beta90).Append(",");
|
|
if (double.IsNaN(valuation.Beta720) || double.IsInfinity(valuation.Beta720)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.Beta720).Append(",");
|
|
if (double.IsNaN(valuation.MarketCap) || double.IsInfinity(valuation.MarketCap)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.MarketCap).Append(",");
|
|
if (double.IsNaN(valuation.EarningsYield) || double.IsInfinity(valuation.EarningsYield)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.EarningsYield).Append(",");
|
|
if (double.IsNaN(valuation.EBIT) || double.IsInfinity(valuation.EBIT)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.EBIT).Append(",");
|
|
if (double.IsNaN(valuation.EnterpriseValue) || double.IsInfinity(valuation.EnterpriseValue)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.EnterpriseValue).Append(",");
|
|
if (double.IsNaN(valuation.TLBRankROIC) || double.IsInfinity(valuation.TLBRankROIC)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.TLBRankROIC).Append(",");
|
|
if (double.IsNaN(valuation.FundamentalValue) || double.IsInfinity(valuation.FundamentalValue)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.FundamentalValue).Append(",");
|
|
if (double.IsNaN(valuation.NetCurrentAssetValuePerShare) || double.IsInfinity(valuation.NetCurrentAssetValuePerShare)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.NetCurrentAssetValuePerShare).Append(",");
|
|
if (double.IsNaN(valuation.LatestROIC) || double.IsInfinity(valuation.LatestROIC)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.LatestROIC).Append(",");
|
|
if (null==valuation.Sector) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.Sector)).Append(",");
|
|
if (null==valuation.Industry) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(valuation.Industry)).Append(",");
|
|
if (double.IsNaN(valuation.DebtToEquity) || double.IsInfinity(valuation.DebtToEquity)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.DebtToEquity).Append(",");
|
|
sb.Append(valuation.SEC13).Append(",");
|
|
if (Utility.IsEpoch(valuation.SEC13FilingDate)) sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuation.SEC13FilingDate))).Append(",");
|
|
if (double.IsNaN(valuation.LatestROC) || double.IsInfinity(valuation.LatestROC)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.LatestROC).Append(",");
|
|
if (double.IsNaN(valuation.TLBRankROC) || double.IsInfinity(valuation.TLBRankROC)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.TLBRankROC).Append(",");
|
|
if (double.IsNaN(valuation.OperatingEarnings) || double.IsInfinity(valuation.OperatingEarnings)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.OperatingEarnings).Append(",");
|
|
if (double.IsNaN(valuation.AMRank) || double.IsInfinity(valuation.AMRank)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AMRank).Append(",");
|
|
if (double.IsNaN(valuation.AcquirersMultiple) || double.IsInfinity(valuation.AcquirersMultiple)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.AcquirersMultiple).Append(",");
|
|
if (double.IsNaN(valuation.DividendYield) || double.IsInfinity(valuation.DividendYield)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.DividendYield).Append(",");
|
|
if (double.IsNaN(valuation.OperatingCashflow) || double.IsInfinity(valuation.OperatingCashflow)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.OperatingCashflow).Append(",");
|
|
if (double.IsNaN(valuation.SharesOutstanding) || double.IsInfinity(valuation.SharesOutstanding)) sb.Append("null").Append(",");
|
|
else sb.Append(valuation.SharesOutstanding).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(modified))).Append(")");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
}
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
MDTrace.WriteLine(LogLevel.DEBUG,strQuery);
|
|
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlTransaction) sqlTransaction.Dispose();
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static void RemoveValuationBySymbol(String symbol,DateTime valuationDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlTransaction sqlTransaction=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction=sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
sb.Append("delete from valuations where symbol='").Append(symbol).Append("'");
|
|
sb.Append(" and valuation_date=").Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuationDate)));
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection,sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
sqlTransaction.Commit();
|
|
return;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static bool RemoveValuation(DateTime valuationDate,MySqlTransaction sqlTransaction)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("delete from valuations where valuation_date=");
|
|
sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuationDate)));
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection,sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|