static void Main( string [] args ) { StockPortfolioEntities7 spe = new StockPortfolioEntities7( ); var infos = spe.InfoCompanies; foreach ( var ii in infos ) Console.WriteLine( ii.Name ); string oldPath = @"C:\Users\KNM\Desktop\halfway.csv"; string newerPath = @"C:\Users\KNM\Documents\GitHub\KNMFin\DebugTest\DATA\sp500tickers.csv"; String [] lines; using ( System.IO.StreamReader sr = new System.IO.StreamReader(oldPath) ) { string [] separator = new string [] { "\r\n" }; lines = sr.ReadToEnd( ).Split( separator, StringSplitOptions.None ); } List<string []> items = new List<string []>( ); Dictionary<string, string []> vals = new Dictionary<string, string []>( ); string [] sep = new string [] { "," }; int dummy = 0; foreach ( string s in lines ) { if ( dummy == 0 ) dummy++; else{ var t = s.Split( sep, StringSplitOptions.None ); items.Add(t); if ( !vals.ContainsKey( t [ 0 ] ) && t[0] != string.Empty) vals.Add( t [ 0 ], new string [] { t [ 1 ], t [ 2 ], t [ 3 ] } ); } } Dictionary<string, string []> hasResult = new Dictionary<string, string []>( ); List<string> noResults = new List<string>( ); HashSet<CompanyInfo> companyInfos = new HashSet<CompanyInfo>( ); // lines = GetDJIATickers( ); CompanyInfo ci; int cID = spe.InfoCompanies.Count( ); int every50 = 0; foreach ( KeyValuePair<string, string[]> kvp in vals ) { if ( kvp.Key == null || kvp.Key.Replace(" ", "") == string.Empty ) continue; ci = new CompanyInfo( kvp.Key, true ); if ( ci.Ticker != null ) { var icQ = spe.InfoCompanies.Where( i => i.Ticker.ToUpper() == ci.Ticker.ToUpper()).FirstOrDefault(); InfoCompany ic; if ( icQ == null) { ic = new InfoCompany( ); ic.Name = ci.Name; if ( ci.Name.Length > 150 ) ic.Name = ic.Name.Substring( 0, 150 ); ic.Ticker = ci.Ticker; } else { ic = icQ; } if ( ci.Exchange != null) { var exc = spe.InfoExchanges.Where( i => i.Name == ci.Exchange ).FirstOrDefault( ); if ( exc == null){ ic.InfoExchanges.Add( new InfoExchange { Name = ci.Exchange } ); spe.SaveChanges( ); } else { ic.InfoExchanges.Add( exc ); } if ( ci.KeyStats.Count( ) > 0 ) { int i = 0; foreach ( KeyStatItems ks in ci.KeyStats ) { bool annualorQ = i == 0 ? false : true; KeyAccountingStat ksDB; var kasQuery = spe.KeyAccountingStats.Where( j => j.Annual == annualorQ && j.CompanyID == ic.ID ).FirstOrDefault( ); if ( kasQuery == null ) { ksDB = new KeyAccountingStat( ); if ( ks.CDPscore != null ) { if ( ks.CDPscore.Item1 != null ) ksDB.CDPScoreN = ks.CDPscore.Item1; else ksDB.CDPScoreN = null; if ( ks.CDPscore.Item2 != null ) ksDB.CDPScoreL = ks.CDPscore.Item2; else ksDB.CDPScoreL = null; } else { ksDB.CDPScoreN = null; ksDB.CDPScoreL = null; } ksDB.Annual = i == 0 ? false : true; ksDB.EBITDMargin = ks.EBITDmargin; ksDB.Employees = (int?)ks.Employees; ksDB.NetProfitMargin = ks.NetProfitMargin; ksDB.OperatingMargin = ks.OperatingMargin; ksDB.Period = DateTime.Now; ksDB.ReturnOnAverageAssets = ks.ReturnOnAverageAssets; ksDB.ReturnOnAverageEquity = ks.ReturnOnAverageEquity; ic.KeyAccountingStats.Add( ksDB ); } i++; } } if(ic.Name == "Uranium Resources, Inc.") { var dbb = 1; } // TODO: Add update case if ( ic.FinancialSnapshots.Count > 0 ) { } else { var fss = new FinancialSnapshot( ); fss.QueryDate = DateTime.Now; // Nons-sensical value if this condition is false if(ci.Beta < 100) fss.Beta = (decimal?)ci.Beta; fss.ClosePrice = (decimal?)ci.Close; fss.OpenPrice = (decimal?)ci.Open; fss.Dividend = (decimal?)ci.Dividend; fss.DividendYield = (decimal?)ci.DividendYield; fss.EarningsPerShare = (decimal?)ci.EarningsPerShare; fss.FiftyTwoWeekHigh = (decimal?)ci.FiftyTwoWeekHigh; fss.FiftyTwoWeekLow = (decimal?)ci.FiftyTwoWeekLow; fss.InstitutionalOwnership = (decimal?)ci.InstitutionalOwnership; fss.MarketCap = (decimal?)ci.MarketCap; fss.PriceEarnings = (decimal?)ci.PriceEarnings; fss.RangeHigh = (decimal?)ci.RangeHigh; fss.RangeLow = (decimal?)ci.RangeLow; fss.Shares = (decimal?)ci.Shares; fss.VolumeAverage = (decimal?)ci.VolumeAverage; fss.VolumeDaily = (decimal?)ci.VolumeTotal; ic.FinancialSnapshots.Add( fss ); } if ( ci.BalanceSheets != null ) { foreach ( KNMFin.Google.BalanceSheet bs in ci.BalanceSheets ) { if ( ic.BalanceSheets.Where( i => i.PeriodEnd == bs.PeriodEnd && i.InfoCompanies.FirstOrDefault( ) != null ? i.InfoCompanies.FirstOrDefault( ).ID == ic.ID : false ).FirstOrDefault( ) == null ) ic.BalanceSheets.Add( balancesheetDB( bs ) ); } } if ( ci.IncomeStatements != null ) { foreach ( KNMFin.Google.IncomeStatement inc in ci.IncomeStatements ) { if ( ic.IncomeStatements.Where( i => i.PeriodEnd == inc.PeriodEnd && i.InfoCompanies.FirstOrDefault( ) != null ? i.InfoCompanies.FirstOrDefault( ).ID == ic.ID : false ).FirstOrDefault( ) == null ) ic.IncomeStatements.Add( incomestatementDB( inc, spe ) ); } int db = 1; } if ( ci.CashFlowStatements != null ) { foreach ( KNMFin.Google.CashFlowStatement cfs in ci.CashFlowStatements ) { if ( ic.StatementOfCashFlows.Where( i => i.PeriodEnd == cfs.PeriodEnd && i.InfoCompanies.FirstOrDefault( ) != null ? i.InfoCompanies.FirstOrDefault( ).ID == ic.ID : false ).FirstOrDefault( ) == null ) ic.StatementOfCashFlows.Add( cashflowDB( cfs ) ); } } var industryName = kvp.Value [ 2 ]; var industryQ = spe.InfoIndustries.Where( i => i.Name == industryName ).FirstOrDefault( ); if(industryQ == null){ var iName = kvp.Value[2]; ic.InfoIndustries.Add( new InfoIndustry { Name = iName } ); try { spe.SaveChanges( ); } catch(Exception ex) { var iex = ex.InnerException; } ic.InfoIndustries.Add( spe.InfoIndustries.Where( i => i.Name == iName ).FirstOrDefault( ) ); } else{ ic.InfoIndustries.Add(industryQ); } var sectorName = kvp.Value [ 1 ]; var sectorQ = spe.InfoSectors.Where( i => i.Name == sectorName ).FirstOrDefault( ); if ( sectorQ == null ) { var sName = kvp.Value [ 1 ]; ic.InfoSectors.Add( new InfoSector { Name = sName } ); spe.SaveChanges( ); } else { ic.InfoSectors.Add( sectorQ ); } } if(icQ == null) spe.InfoCompanies.Add( ic ); every50++; if ( every50 == 50 ) { every50 = 0; try { spe.SaveChanges( ); Console.WriteLine( "Saving 50 results into database: " + DateTime.Now.ToShortTimeString( ) ); } catch ( System.Data.Entity.Validation.DbEntityValidationException dbEx ) { foreach ( var validationErrors in dbEx.EntityValidationErrors ) { foreach ( var validationError in validationErrors.ValidationErrors ) { Console.WriteLine( "Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage ); System.Diagnostics.Trace.TraceInformation( "Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage ); } } } catch ( Exception ex ) { var exin = ex.InnerException; } } if ( !hasResult.ContainsKey( kvp.Key ) ) { var t1 = kvp.Key; var t2 = kvp.Value; t2 [ 1 ] = ci.Ticker; hasResult.Add( kvp.Key, kvp.Value ); } } else { noResults.Add( kvp.Key ); } Console.Write( "->Processed: " + kvp.Key ); } try { spe.SaveChanges( ); } catch ( System.Data.Entity.Validation.DbEntityValidationException dbEx ) { foreach ( var validationErrors in dbEx.EntityValidationErrors ) { foreach ( var validationError in validationErrors.ValidationErrors ) { Console.WriteLine( "Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage ); } } } catch ( Exception ex ) { var wutttt = ex.InnerException; } finally { using ( var writer = new StreamWriter( @"C:\users\knm\desktop\ticker_results.csv" ) ) { foreach ( KeyValuePair<string, string []> kvp in hasResult ) { writer.WriteLine( "{0}, {1}, {2}, {3}", kvp.Key, kvp.Value [ 0 ], kvp.Value [ 1 ], kvp.Value [ 2 ] ); } } using ( var writer = new StreamWriter( @"C:\users\knm\desktop\no_ticker_results.csv" ) ) { foreach ( string s in noResults ) { writer.WriteLine( s ); } } } var wut = 1; }
static IncomeStatement incomestatementDB(KNMFin.Google.IncomeStatement inc, StockPortfolioEntities7 spe) { var incDB = new IncomeStatement( ); incDB.Accounting_Change = inc.Accounting_Change; incDB.Annual = inc.Period == Period.Annual ? true : false; incDB.Basic_EPS_after_Stock_Based_Comp_Expense = inc.Basic_EPS_after_Stock_Based_Comp_Expense; incDB.Basic_EPS_Excluding_Extraordinary_Items = inc.Basic_EPS_Excluding_Extraordinary_Items; incDB.Basic_EPS_Including_Extraordinary_Items = inc.Basic_EPS_Including_Extraordinary_Items; incDB.Basic_Normalized_EPS = inc.Basic_Normalized_EPS; incDB.Basic_Weighted_Average_Shares = inc.Basic_Weighted_Average_Shares; incDB.Cost_of_Revenue_Total = inc.Cost_of_Revenue_Total; incDB.Depreciation_and_Amortization = inc.Depreciation_and_Amortization; incDB.Depreciation_Supplemental = inc.Depreciation_Supplemental; incDB.Diluted_EPS_after_Stock_Based_Comp_Expense = inc.Diluted_EPS_after_Stock_Based_Comp_Expense; incDB.Diluted_EPS_Excluding_Extraordinary_Items = inc.Diluted_EPS_Excluding_Extraordinary_Items; incDB.Diluted_EPS_Including_Extraordinary_Items = inc.Diluted_EPS_Including_Extraordinary_Items; incDB.Diluted_Normalized_EPS = inc.Diluted_Normalized_EPS; incDB.Diluted_Weighted_Average_Shares = inc.Diluted_Weighted_Average_Shares; incDB.Dilution_Adjustment = inc.Dilution_Adjustment; incDB.Discontinued_Operations = inc.Discontinued_Operations; incDB.Dividends_per_Share__less__Common_Stock_Primary_Issue = inc.Dividends_per_Share__less__Common_Stock_Primary_Issue; incDB.Effect_of_Special_Items_on_Income_Taxes = inc.Effect_of_Special_Items_on_Income_Taxes; incDB.Equity_In_Affiliates = inc.Equity_In_Affiliates; incDB.Extraordinary_Item = inc.Extraordinary_Item; incDB.Gain___Loss___on_Sale_of_Assets = inc.Gain___Loss___on_Sale_of_Assets; incDB.Gross_Dividends__less__Common_Stock = inc.Gross_Dividends__less__Common_Stock; incDB.Gross_Profit = inc.Gross_Profit; incDB.Income_After_Tax = inc.Income_After_Tax; incDB.Income_Available_to_Common_Excl_Extra_Items = inc.Income_Available_to_Common_Excl_Extra_Items; incDB.Income_Available_to_Common_Incl_Extra_Items = inc.Income_Available_to_Common_Incl_Extra_Items; incDB.Income_Before_Tax = inc.Income_Before_Tax; incDB.Income_Taxes_Ex_Impact_of_Special_Items = inc.Income_Taxes_Ex_Impact_of_Special_Items; incDB.Interest_Income__Expense___Net_NonOperating = inc.Interest_Expense__Income___less_Net_Operating; incDB.Interest_Expense__Income___less_Net_Operating = inc.Interest_Income__Expense___Net_NonOperating; incDB.Minority_Interest = inc.Minority_Interest; incDB.Net_Income = inc.Net_Income; incDB.Net_Income_after_Stock_Based_Comp_Expense = inc.Net_Income_after_Stock_Based_Comp_Expense; incDB.Net_Income_Before_Extra_Items = inc.Net_Income_Before_Extra_Items; incDB.Normalized_Income_After_Taxes = inc.Normalized_Income_After_Taxes; incDB.Normalized_Income_Avail_to_Common = inc.Normalized_Income_Avail_to_Common; incDB.Normalized_Income_Before_Taxes = inc.Normalized_Income_Before_Taxes; incDB.Operating_Income = inc.Operating_Income; incDB.Other_Net = inc.Other_Net; incDB.Other_Operating_Expenses_Total = inc.Other_Operating_Expenses_Total; incDB.Other_Revenue_Total = inc.Other_Revenue_Total; incDB.PeriodEnd = inc.PeriodEnd; incDB.Preferred_Dividends = inc.Preferred_Dividends; incDB.Research_and_Development = inc.Research_and_Development; incDB.Revenue = inc.Revenue; incDB.Selling_and_General_and_Admin_Expenses_Total = inc.Selling_and_General_and_Admin_Expenses_Total; incDB.Total_Operating_Expense = inc.Total_Operating_Expense; incDB.Total_Revenue = inc.Total_Revenue; incDB.Total_Special_Items = inc.Total_Special_Items; incDB.Unusual_Expense___Income__ = inc.Unusual_Expense___Income__; int id = spe.IncomeStatements.Count() + 1; incDB.ID = id; return incDB; }