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