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