private List <KeyRatio> ExecuteKeyRatioReaderQuery(MySqlCommand cmd) { var keyRatioList = new List <KeyRatio>(); if (this.OpenConnection() == true) { MySqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { var keyRatio = new KeyRatio(); keyRatio.IdKeyRatio = (uint)dataReader["idKeyRatio"]; keyRatio.Name = (String)dataReader["Name"]; keyRatio.Value = (String)dataReader["Value"]; keyRatio.Year = (System.Int32)dataReader["YearDate"]; keyRatioList.Add(keyRatio); } dataReader.Close(); this.CloseConnection(); return(keyRatioList); } else { return(keyRatioList); } }
private static void AddCik() { using (var db = new LiteDatabase(Database)) { var success = false; string cik; var dbKeyRatio = db.GetCollection <KeyRatio>("keyRatios"); KeyRatio result = null; do { Console.WriteLine("Ticker of company to add CIK:"); var ticker = Console.ReadLine(); switch (ticker) { case null: Console.WriteLine("Invalid ticker"); continue; case "back": return; } ticker = ticker.ToUpper(); //Find ticker, throw error if not found result = dbKeyRatio.FindOne(Query.EQ("Ticker", ticker)); if (result == null) { Console.WriteLine("Invalid ticker"); } else { success = true; } } while (!success); success = false; do { Console.WriteLine("CIK (please include beginning zeros!): "); cik = Console.ReadLine(); switch (cik) { case null: Console.WriteLine("Invalid metric"); continue; case "back": return; default: success = true; break; } } while (!success); result.Cik = cik; try { dbKeyRatio.Update(result); Console.WriteLine("CIK updated successfully!"); Console.WriteLine("Press any key to continue..."); Console.ReadKey(); } catch (Exception e) { Utility.WriteToErrorLog(e); Console.WriteLine("Ticker's CIK could not be edited."); Console.WriteLine("Please see program's error log for more details"); } } }
private static void Manual() { using (var db = new LiteDatabase(Database)) { var success = false; string metric; var dbKeyRatio = db.GetCollection <KeyRatio>("keyRatios"); KeyRatio result = null; do { Console.WriteLine("Ticker of company to edit:"); var ticker = Console.ReadLine(); switch (ticker) { case null: Console.WriteLine("Invalid ticker"); continue; case "back": return; } ticker = ticker.ToUpper(); //Find ticker, throw error if not found result = dbKeyRatio.FindOne(Query.EQ("Ticker", ticker)); if (result == null) { Console.WriteLine("Invalid ticker"); } else { success = true; } } while (!success); success = false; do { Console.WriteLine("Metric to edit:"); metric = Console.ReadLine(); switch (metric) { case null: Console.WriteLine("Invalid metric"); continue; case "back": return; } metric = metric.ToLower(); if (metric == "sector" || metric == "industry" || metric == "subindustry") { success = true; } else { Console.WriteLine("Invalid metric"); } } while (!success); Console.WriteLine("New value:"); var newValue = Console.ReadLine(); if (newValue == "back") { return; } switch (metric) { case "sector": result.Sector = newValue; break; case "industry": result.Industry = newValue; break; case "subindustry": result.Subindustry = newValue; break; } try { dbKeyRatio.Update(result); Console.WriteLine("Database entry written successfully!"); Console.WriteLine( "Company " + result.Name + " (" + result.Ticker + ")'s " + metric + " is now '" + newValue + "'"); Console.WriteLine("Press any key to continue..."); Console.ReadKey(); } catch (Exception e) { Utility.WriteToErrorLog(e); Console.WriteLine("Ticker could not be edited."); Console.WriteLine("Please see program's error log for more details"); } } }
public async Task <List <KeyRatio> > ScrapeKeyRatio(string ticker, string apiKey) { #region Data from QuickFS string url = "https://api.quickfs.net/stocks/" + ticker + ":US/ratios/Annual/" + apiKey; var helper = new WebHelper(); var request = await helper.ComposeWebRequestGet(url); var result = await helper.CallWebRequest(request); result = result.Replace("<\\/td>", ""); result = result.Replace("<\\/tr>", ""); result = result.Replace("$", ""); result = result.Replace(",", "."); var html = new HtmlDocument(); html.LoadHtml(result); var htmlNodes = html.DocumentNode.Descendants("td").ToList(); var numberOfColumns = html.DocumentNode.SelectNodes("//tr[@class='thead']").Descendants("td").ToList().Count(); var numberOfRows = htmlNodes.Count / numberOfColumns; var count = 1; #endregion #region DataOrganization var namesList = new List <string>(); var keyRatios = new List <KeyRatio>(); var valuesFinalList = new List <float>(); for (var i = 1; i < numberOfColumns; i++) { var extractedValuesList = new List <ExtractedValues>(); for (var h = 1; h < numberOfRows; h++) { var name = htmlNodes[h * numberOfColumns].InnerText; if (name == "Revenue" || name == "EBITDA" || name == "Operating Income" || name == "Free Cash Flow" || name == "Book Value" || name == "Tangible Book Value") { name = name + h; } namesList.Add(name); } var parsedYear = int.TryParse(htmlNodes[i].InnerText, out int yearNumber); //if (!parsedYear) return; lançar exceção for (var j = 1; j < numberOfRows; j++) { var name = htmlNodes[j * numberOfColumns].InnerText; var valuesList = new List <string>(); foreach (var item in htmlNodes) { var htmlValue = item.GetAttributeValue("data-value", null); valuesList.Add(htmlValue); } var valuesFromNodes = valuesList[(j * numberOfColumns) + count]; bool parsedFloat = float.TryParse(valuesFromNodes, NumberStyles.Float, CultureInfo.InvariantCulture, out float valuesFloat); valuesFinalList.Add(valuesFloat); if (yearNumber != 0 && name != "" && name != "Returns") { var baseItems = new BaseItem(); var extractedValues = new ExtractedValues(); extractedValues.Year = yearNumber; baseItems.Name = name; baseItems.Value = valuesFloat; extractedValues.Items.Add(baseItems); extractedValuesList.Add(extractedValues); } } count++; #endregion #region Add to KeyRatio foreach (var extractedItem in extractedValuesList) { var keyRatio = new KeyRatio(); var props = keyRatio.GetType().GetProperties(); keyRatio.Year = extractedItem.Year; foreach (var prop in props) { var displayAttribute = prop.GetCustomAttributes <DisplayAttribute>().SingleOrDefault(); if (displayAttribute != null) { var item = extractedItem.Items.SingleOrDefault(i => i.Name == displayAttribute.Name); if (item != null) { prop.SetValue(keyRatio, item.Value); } } } if (keyRatio.Year != 0) { keyRatios.Add(keyRatio); } } #endregion } Random rnd = new Random(); await Task.Delay(TimeSpan.FromSeconds(rnd.Next(1, 10))); return(keyRatios); }
private static bool ParseExcel(string ticker, ref KeyRatio result, bool amend) { //Open Excel File var file = new FileInfo(Temp + ticker + ".xlsx"); using (var package = new ExcelPackage(file)) { var worksheet = package.Workbook.Worksheets[1]; //Checks for inconsistent spreadsheet try { var name = worksheet.Cells[1, 1].Value.ToString().Trim(); name = name.Substring(46); } catch (NullReferenceException) { Utility.WriteToLog(ticker, "Blank Excel document", ErrorLog); return(false); } catch (ArgumentOutOfRangeException) //Catches "N/A" from QuantizeString method { Utility.WriteToLog(ticker, "Blank Excel document", ErrorLog); return(false); } catch (Exception) { Utility.WriteToLog(ticker, "Blank or unstandardized Excel document", ErrorLog); return(false); } string currency; try { currency = worksheet.Cells[4, 1].Value.ToString().Trim(); if (!currency.Contains("Revenue")) //Data is inconsistent { Utility.WriteToLog(ticker, "Data is inconsistent", ErrorLog); return(false); } currency = currency.Substring(8, 3); //Grabs currency type } catch (ArgumentOutOfRangeException) { Utility.WriteToLog(ticker, "Morningstar didn't have data on selected ticker", ErrorLog); return(false); } catch (Exception) { Utility.WriteToLog(ticker, "Morningstar didn't have data on selected ticker", ErrorLog); return(false); } //Check if Morningstar has current financial information //This has been an error in the past: // The SEC list had the 10-K filed with it, but Morningstar didn't have the updated data yet try { var yearDate = GetYear(worksheet.Cells[3, 11].Value.ToString()); if (yearDate < _currentYear && !amend) { Console.WriteLine("Skipping " + ticker + "..."); Utility.WriteToLog(ticker, "Skipping, as Morningstar didn't have up-to-date data on it", ErrorLog); return(false); } } catch (Exception e) { Utility.WriteToErrorLog(e); } //Load fiscal years to new array if (!amend) { var fYears = new string[result.FiscalYears.Length + 1]; for (var i = 0; i < result.FiscalYears.Length; i++) { fYears[i] = result.FiscalYears[i]; } var refIndex = fYears.Length - 1; fYears[refIndex] = worksheet.Cells[3, 11].Value.ToString(); result.FiscalYears = fYears; } //Inflate dictionaries var locNormal = File.ReadAllLines(Data + "LOC_normal.txt"); var locDynamic = File.ReadAllLines(Data + "LOC_dynamic.txt"); var normal = locNormal.Select(v => v.Split(',')).ToDictionary(tmp => tmp[1], tmp => tmp[0]); var dynam = locDynamic.Select(v => v.Split(',')).ToDictionary(tmp => tmp[1], tmp => tmp[0] + " " + currency); var totalRow = worksheet.Dimension.End.Row; //This number is for when finding the Key Ratio -> Growth rows in the spreadsheet var growthRatioRow = 0; //Find all metrics that cannot be found in spreadsheet var wsMetrics = new List <string>(); for (var i = 1; i <= totalRow; i++) { var header = worksheet.Cells[i, 1].Value; if (header == null) { continue; } if (header.ToString() == "Revenue %") { growthRatioRow = i + 1; //References first meaningful line } wsMetrics.Add(header.ToString()); } foreach (var s in normal) { if (!wsMetrics.Any(s.Value.Contains)) { Utility.WriteToLog(ticker, s + " not found", ErrorLog); } } foreach (var d in dynam) { if (!wsMetrics.Exists(x => x.Contains(d.Value))) { Utility.WriteToLog(ticker, d + " not found", ErrorLog); } } //Loop to get all normal metrics for (var i = 1; i <= totalRow; i++) { var header = worksheet.Cells[i, 1].Value; if (header == null) { continue; } if (!normal.ContainsValue(header.ToString())) { continue; } var previousDataKey = normal.FirstOrDefault(x => x.Value.Equals(header.ToString())).Key; result.FinancialData[previousDataKey] = GetData(worksheet, result.FinancialData[previousDataKey], i, amend); } //Loop to get all metrics with currency in name for (var i = 1; i <= totalRow; i++) { var header = worksheet.Cells[i, 1].Value; if (header == null) { continue; } if (!dynam.Any(x => header.ToString().Contains(x.Value))) { continue; } var previousDataKey = dynam.FirstOrDefault(x => header.ToString().Contains(x.Value)).Key; result.FinancialData[previousDataKey] = GetData(worksheet, result.FinancialData[previousDataKey], i, amend); } var percentArray = new[] { "RGYY", "RGY3", "RGY5", "RGY10", "OIGYY", "OIGY3", "OIGY5", "OIGY10", "NIGYY", "NIGY3", "NIGY5", "NIGY10", "EPSGYY", "EPSGY3", "EPSGY5", "EPSGY10" }; var pos = 0; //16 stands for how many rows we need for (var i = 0; i < 19; i++) { var index = percentArray[pos]; if (i == 4 || i == 9 || i == 14) { i++; } result.FinancialData[index] = GetData(worksheet, result.FinancialData[index], growthRatioRow + i, amend); pos++; } } return(true); }
private static KeyRatio ParseExcel(string ticker, string dataFolder) { var keyRatio = new KeyRatio(); //Open Excel File var file = new FileInfo(dataFolder + ticker + ".xlsx"); using (var package = new ExcelPackage(file)) { var worksheet = package.Workbook.Worksheets[1]; //Get the name of company string name; try { name = worksheet.Cells[1, 1].Value.ToString().Trim(); name = name.Substring(46); } catch (NullReferenceException) { Utility.WriteToLog(ticker, "Blank Excel document", ErrorLog); return(null); } catch (ArgumentOutOfRangeException) //Catches "N/A" from QuantizeString method { Utility.WriteToLog(ticker, "Blank Excel document", ErrorLog); return(null); } catch (Exception) { Utility.WriteToLog(ticker, "Blank or unstandardized Excel document", ErrorLog); return(null); } keyRatio.Name = name; keyRatio.Ticker = ticker; //Currency type string currency; try { currency = worksheet.Cells[4, 1].Value.ToString().Trim(); if (!currency.Contains("Revenue")) //Data is inconsistent { Utility.WriteToLog(ticker, "Data is inconsistent", ErrorLog); return(null); } currency = currency.Substring(8, 3); //Grabs currency type } catch (ArgumentOutOfRangeException) { Utility.WriteToLog(ticker, "Morningstar didn't have data on selected ticker", ErrorLog); return(null); } catch (Exception) { Utility.WriteToLog(ticker, "Morningstar didn't have data on selected ticker", ErrorLog); return(null); } keyRatio.CurrencyType = currency; //Fiscal Years //Gets the first date on the spreadsheet, gets the month from the date, //then converts the month number to a word; basically: 2007-12 --> December keyRatio.FiscalYearEnd = GetMonthName( GetMonth( worksheet.Cells[3, 2].Value.ToString()).ToString()); keyRatio.FiscalYears = new string[10]; for (var i = 2; i < 12; i++) { keyRatio.FiscalYears[i - 2] = worksheet.Cells[3, i].Value.ToString(); } //Inflate dictionaries var locNormal = File.ReadAllLines(Data + "LOC_normal.txt"); var locDynamic = File.ReadAllLines(Data + "LOC_dynamic.txt"); var normal = locNormal.Select(v => v.Split(',')).ToDictionary(tmp => tmp[1], tmp => tmp[0]); var dynam = locDynamic.Select(v => v.Split(',')).ToDictionary(tmp => tmp[1], tmp => tmp[0] + " " + currency); var totalRow = worksheet.Dimension.End.Row; keyRatio.FinancialData = new Dictionary <string, double[]>(); //This number is for when finding the Key Ratio -> Growth rows in the spreadsheet var growthRatioRow = 0; //Find all metrics that cannot be found in spreadsheet var wsMetrics = new List <string>(); for (var i = 1; i <= totalRow; i++) { var header = worksheet.Cells[i, 1].Value; if (header == null) { continue; } if (header.ToString() == "Revenue %") { growthRatioRow = i + 1; //References first meaningful line } wsMetrics.Add(header.ToString()); } foreach (var s in normal) { if (!wsMetrics.Any(s.Value.Contains)) { Utility.WriteToLog(ticker, s + " not found", ErrorLog); } } foreach (var d in dynam) { if (!wsMetrics.Exists(x => x.Contains(d.Value))) { Utility.WriteToLog(ticker, d + " not found", ErrorLog); } } //Loop to get all normal metrics //Uses "Matches" method for (var i = 1; i <= totalRow; i++) { var header = worksheet.Cells[i, 1].Value; if (header == null) { continue; } if (!normal.ContainsValue(header.ToString())) { continue; } keyRatio.FinancialData.Add( normal.FirstOrDefault(x => x.Value.Equals(header.ToString())).Key, GetData(worksheet, i)); } //Loop to get all metrics with currency in name for (var i = 1; i <= totalRow; i++) { var header = worksheet.Cells[i, 1].Value; if (header == null) { continue; } if (!dynam.Any(x => header.ToString().Contains(x.Value))) { continue; } keyRatio.FinancialData.Add( dynam.FirstOrDefault(x => header.ToString().Contains(x.Value)).Key, GetData(worksheet, i)); } //Revenue % keyRatio.FinancialData.Add("RGYY", GetData(worksheet, growthRatioRow)); keyRatio.FinancialData.Add("RGY3", GetData(worksheet, growthRatioRow + 1)); keyRatio.FinancialData.Add("RGY5", GetData(worksheet, growthRatioRow + 2)); keyRatio.FinancialData.Add("RGY10", GetData(worksheet, growthRatioRow + 3)); //Operating Income % keyRatio.FinancialData.Add("OIGYY", GetData(worksheet, growthRatioRow + 5)); keyRatio.FinancialData.Add("OIGY3", GetData(worksheet, growthRatioRow + 6)); keyRatio.FinancialData.Add("OIGY5", GetData(worksheet, growthRatioRow + 7)); keyRatio.FinancialData.Add("OIGY10", GetData(worksheet, growthRatioRow + 8)); //Net Income % keyRatio.FinancialData.Add("NIGYY", GetData(worksheet, growthRatioRow + 10)); keyRatio.FinancialData.Add("NIGY3", GetData(worksheet, growthRatioRow + 11)); keyRatio.FinancialData.Add("NIGY5", GetData(worksheet, growthRatioRow + 12)); keyRatio.FinancialData.Add("NIGY10", GetData(worksheet, growthRatioRow + 13)); //EPS % keyRatio.FinancialData.Add("EPSGYY", GetData(worksheet, growthRatioRow + 15)); keyRatio.FinancialData.Add("EPSGY3", GetData(worksheet, growthRatioRow + 16)); keyRatio.FinancialData.Add("EPSGY5", GetData(worksheet, growthRatioRow + 17)); keyRatio.FinancialData.Add("EPSGY10", GetData(worksheet, growthRatioRow + 18)); } return(keyRatio); }