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