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