//To get all items by running this on all tickers.
public static void GetAllFRItemFromNASDAQ(string[] tickers, string reportType)
{
string periodType = "Annual";
string numPeriods = "10";
string reportParam = FinancialReportsConfig.params_NASDAQ[reportType];
string periodParam = FinancialReportsConfig.params_NASDAQ[periodType];
var keys = (Dictionary<string, int>)FinancialReportsConfig.mappings_NASDAQ[reportType];
List<string[]> list = new List<string[]>();
list.Add(new string[] { "ticker", "not in key", "previous key", "next key", "number" });
foreach (string ticker in tickers)
{
Dictionary<string, object> data = new Dictionary<string, object>();
try
{
WebData webData = new WebData(string.Format(Constants.NASDAQFinancialReportingURL, reportParam, periodParam, numPeriods, ticker));
data = webData.ReadNASDAQFRHtmlTable().ElementAt(1);
}
catch
{
}
data.Each((d, i) =>
{
int nexti = i + 1 == data.Count ? i : i + 1;
if (!keys.ContainsKey(d.Key))
list.Add(new string[] { ticker, d.Key, data.ElementAt(i - 1).Key, data.ElementAt(nexti).Key, i.ToString() });
}
);
//list.Add(new string[]{"","","","",""});
}
Excel.Worksheet ws = ExcelUtil.Worksheet("Test_" + reportType);
ws.Range("A2").Resize(list.Count, list[0].Length).Formula = ExcelUtil.To2DArray(list);
}
private Task<bool> LoadAllQuotesData()
{
WebData webData = new WebData(Constants.FinvizQuotesURL);
return webData.GetDataFromCSVStream().Then(
response =>
{
object[][] csvData = response.Result;
maxDataRow = csvData.Count() - 1;
maxDataColumn = csvData[0].Length;
Excel.Worksheet ws = ExcelUtil.Worksheet("All Data");
ws.Range(ALL_DATA_HEADER_ORIGIN).Resize(maxDataRow + 1, maxDataColumn).Formula = ExcelUtil.To2DArray(csvData);
ws.Range("HasData").Formula = 1;
ws.Range("RowNum").Formula = maxDataRow;
ws.Range("ColumnNum").Formula = maxDataColumn;
var fields = ScreenerConfig.DataHeader;
allData = csvData.Where((row, i) => i > 0).Select(row => fields.ToDictionary(kv => kv.Key, kv => row[kv.Value]));
return true;
}
);
}
public static bool LoadReportFromNASDAQ(string ticker, string reportType, string periodType, int numPeriods = 10)
{
string reportParam = FinancialReportsConfig.params_NASDAQ[reportType];
string periodParam = FinancialReportsConfig.params_NASDAQ[periodType];
var keys = (Dictionary<string, int>)FinancialReportsConfig.mappings_NASDAQ[reportType];
try
{
WebData webData = new WebData(string.Format(Constants.NASDAQFinancialReportingURL, reportParam, periodParam, numPeriods, ticker));
IEnumerable<Dictionary<string, object>> data = webData.ReadNASDAQFRHtmlTable();
data = data.Reverse();
DataToExcelColumnHeaderTable(data, keys, reportType, reportType.Replace(" ", "") + "_" + periodType, "Right");
return true;
}
catch(Exception e)
{
Excel.Worksheet ws = ExcelUtil.Worksheet("Error");
Excel.Range error = ws.Range("A1");
error.Value = ticker + " " + e.Message;
return false;
}
}
public static Task<bool> LoadReportFromMorningStar(string ticker, string reportType, string periodType, string reportParam)
{
string origin = periodType == "Annual" ? "A2" : "P2";
Excel.Worksheet ws = ExcelUtil.Worksheet(reportType + "_MorningStar");
string period = periodType == "Annual" ? "12" : "3";
WebData webData = new WebData(string.Format(Constants.MSFinancialReportsExportURL, ticker, reportParam, period));
return webData.GetDataFromCSVStream().Then(
(response) =>
{
object[][] csvData = response.Result;
object[,] reportData = ExcelUtil.To2DArray(csvData); // ExcelUtil.CSVDataTo2DArray(csvData, "FirstCol");
ws.Range(origin).Formula = periodType + " Data";
ws.Range(origin).Offset(1).Resize(reportData.GetLength(0), reportData.GetLength(1)).Formula = reportData;
return true;
}
);
}
//date must by mm/dd/yyyy format
public static Task<bool> LoadHistoricalQuotesFromYahoo(string ticker, string start = null, string end = null)
{
Excel.Worksheet ws = ExcelUtil.Worksheet("Quotes");
DateTime endDate = end != null ? DateTime.Parse(end) : DateTime.Today;
DateTime startDate = start != null ? DateTime.Parse(start) : endDate.AddYears(-10);
WebData webData = new WebData(string.Format(YahooFinanceConstants.HistoricalQuotesURL, ticker, startDate.Month - 1, startDate.Day, startDate.Year, endDate.Month, endDate.Day, endDate.Year));
return webData.GetDataFromCSVStream().Then(
(response) =>
{
object[][] csvData = response.Result;
csvData = csvData.Where((d, i) => i > 0).ToArray();
Excel.Range quotesRange = ws.Range("Quotes_History");
Excel.Range origin = quotesRange.Cells[1, 1];
long rowsToInsert = csvData.Length - quotesRange.Rows.Count;
if (rowsToInsert > 0)
origin.Offset(1).Resize(rowsToInsert).EntireRow.Insert();
quotesRange.ClearContents();
origin.Resize(csvData.Length, csvData[0].Length).Formula = ExcelUtil.To2DArray(csvData);
return true;
}
);
}
public static bool LoadEPSFromXbrl(string ticker, int numPeriods = 15)
{
WebData webData = new WebData();
try
{
var data = webData.GetXbrlData(ticker, "10-Q;10-K", numPeriods, "NetIncomeLoss;NetIncomeLossAvailableToCommonStockholdersBasic;EarningsPerShareBasic;EarningsPerShareDiluted;WeightedAverageNumberOfDilutedSharesOutstanding;WeightedAverageNumberOfSharesOutstandingBasic");
//data = data.OrderBy(x => x["EndDate"]);
data = data.Select(
d =>
{
if (d.ContainsKey("NetIncomeLoss") && d.ContainsKey("NetIncomeLossAvailableToCommonStockholdersBasic"))
d.Remove("NetIncomeLoss");
return d;
}
);
var keys = FinancialReportsConfig.mappings_EPS;
var data_Y = data.Where(d => (string)d["FileType"] == "10-K").OrderBy(x => x["EndDate"]);
var data_Q = data.Where(d => (string)d["FileType"] == "10-Q").OrderBy(x => x["EndDate"]);
DataToExcelColumnHeaderTable(data_Y, keys, "Datasheet", "EPS_Y_Xbrl", "Left", true);
DataToExcelColumnHeaderTable(data_Q, keys, "Datasheet", "EPS_Q_Xbrl", "Left", true);
return true;
}
catch (Exception e)
{
Excel.Worksheet ws = ExcelUtil.Worksheet("Error");
Excel.Range error = ws.Range("A1");
error.Value = ticker + " " + e.Message;
return false;
}
}
