void Main() { if (!File.Exists(requestFilePath)) { CreateRequestFile(); } StockRequestInfos stockInfosFromFile = SerializationHelper.LoadXML <StockRequestInfos>(requestFilePath); if (stockInfosFromFile != null) { List <StockRequestInfo> listOfStockInfos = stockInfosFromFile.ListOfStockRequests; foreach (StockRequestInfo stockRequestInfo in listOfStockInfos) { MarketValueInfo marketValue = GetTickerInfo(stockRequestInfo); if (marketValue != null) { CreateExcel(marketValue); if (!UpdateExcel(marketValue)) { ApplicationLog.Instance.WriteError(String.Format("Excel update failed for {0}", stockRequestInfo.Ticker)); } Console.WriteLine(String.Format("Symbole done {0}", marketValue.SYMBOL)); } } } else { ApplicationLog.Instance.WriteError("Request file not in correct format."); } }
private static bool CreateExcel(MarketValueInfo marketValue) { bool bRtnVal = false; Application excelApp = null; Workbook workBook = null; Worksheet workSheet = null; try { var fileName = String.Format("{0}{1}.xlsx", PortfoliExcelPath, excelFileName); if (!File.Exists(fileName)) { excelApp = new Application(); workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); workSheet = workBook.Sheets[1]; PropertyInfo[] properties = marketValue.GetType().GetProperties(); int ncol = 1; foreach (PropertyInfo property in properties) { workSheet.Cells[1, ncol] = property.Name; workSheet.Cells[1, ncol].Font.Bold = true; ncol++; } //To freeze first rwo workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes = true; excelApp.DisplayAlerts = false; workBook.Save(); excelApp.ActiveWorkbook.SaveAs(fileName); workBook.Close(0); excelApp.Quit(); } bRtnVal = true; } catch (Exception ex) { ApplicationLog.Instance.WriteException(ex); } finally { if (workSheet != null) { Marshal.ReleaseComObject(workSheet); } if (workBook != null) { Marshal.ReleaseComObject(workBook); } if (excelApp != null) { Marshal.ReleaseComObject(excelApp); } } return(bRtnVal); }
private static async Task <List <MarketValueInfo> > NewMethod(List <StockRequestInfo> listOfStockInfos) { List <MarketValueInfo> lstRtnVal = new List <MarketValueInfo>(); List <Task <MarketValueInfo> > taskList = (from stockRequst in listOfStockInfos select GetTickerInfoTask(stockRequst)).ToList(); while (taskList.Count > 0) { // Identify the first task that completes. Task <MarketValueInfo> firstFinishedTask = await Task.WhenAny(taskList); // ***Remove the selected task from the list so that you don't // process it more than once. taskList.Remove(firstFinishedTask); // Await the completed task. MarketValueInfo marketValue = await firstFinishedTask; lstRtnVal.Add(marketValue); } return(lstRtnVal); }
private static MarketValueInfo GetTickerInfo(StockRequestInfo stockRequestInfo) { MarketValueInfo rtnVal = null; try { HttpWebRequest req = HttpWebRequest.Create(stockRequestInfo.URL) as HttpWebRequest; req.Accept = "*/*"; req.Host = "nseindia.com"; req.UserAgent = "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:28.0) Gecko/20100101 Firefox/28.0"; req.Method = "GET"; req.KeepAlive = true; String strResult = String.Empty; using (HttpWebResponse res = req.GetResponse() as HttpWebResponse) { using (Stream s = res.GetResponseStream()) { using (StreamReader sr = new StreamReader(s)) { strResult = sr.ReadToEnd().ToString().Trim(); strResult = strResult.Substring(strResult.IndexOf("futLink")); int nt = strResult.IndexOf("</div>"); strResult = strResult.Substring(0, nt); strResult = strResult.Substring(strResult.IndexOf("[{")); nt = strResult.IndexOf("}]"); strResult = strResult.Substring(2, nt); var jsonLikeString = strResult.Split(new String[] { "\",\"" }, StringSplitOptions.None); Dictionary <String, String> dTemp = new Dictionary <string, string>(); foreach (String str in jsonLikeString) { String str1 = str.Replace("\\", String.Empty); str1 = str1.Replace("\"", String.Empty); dTemp.Add(str1.Split(':')[0].Trim().ToUpper(), str1.Split(':')[1].Trim()); } if (dTemp.Count > 0) { rtnVal = new MarketValueInfo(); PropertyInfo[] props = rtnVal.GetType().GetProperties(); foreach (PropertyInfo prop in props) { try { Object propValue = null; TypeConverter typeConverter = TypeDescriptor.GetConverter(prop.PropertyType); try { propValue = typeConverter.ConvertFrom(dTemp[prop.Name]); } catch { if (typeConverter.CanConvertTo(prop.PropertyType) && dTemp.ContainsKey(prop.Name)) { propValue = typeConverter.ConvertTo(null, System.Globalization.CultureInfo.CurrentCulture, dTemp[prop.Name], prop.PropertyType); } } rtnVal.GetType().GetProperty(prop.Name).SetValue(rtnVal, propValue, null); } catch (Exception ex) { ApplicationLog.Instance.WriteException(ex); } } #region keeping for ref only //String previousClose = dTemp["previousClose"]; //String open = dTemp["open"]; //String dayHigh = dTemp["dayHigh"]; //String dayLow = dTemp["dayLow"]; //String lastPrice = dTemp["lastPrice"]; //String closePrice = dTemp["closePrice"]; //String averagePrice = dTemp["averagePrice"]; //String quantityTraded = dTemp["quantityTraded"]; #endregion } else { throw new Exception(String.Format("Webrequest failed for symbol: {0}", stockRequestInfo.Ticker)); } } } } Console.WriteLine(String.Format("Symbol read done: '{0}'", stockRequestInfo.Ticker)); } catch (Exception ex) { ApplicationLog.Instance.WriteError(String.Format("Error in ticker : '{0}'", stockRequestInfo.Ticker)); ApplicationLog.Instance.WriteException(ex); } return(rtnVal); }
private static bool UpdateExcel(MarketValueInfo marketValue) { bool bRtnVal = false; Workbook workBook = null; Application excelApp = null; Worksheet workSheet = null; Range sourceRange = null; try { var fileName = String.Format(@"{0}{1}.xlsx", PortfoliExcelPath, marketValue.SYMBOL); excelApp = new Application(); Workbooks workBooks = excelApp.Workbooks; workBook = excelApp.Workbooks.Open(fileName, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workSheet = workBook.Worksheets[1]; int nColumns = workSheet.UsedRange.Columns.Count; int nRows = workSheet.UsedRange.Rows.Count; for (int i = 1; i <= nColumns; i++) { var columnName = (workSheet.Cells[1, i] as Range).Value as String; var value = marketValue.GetType().GetProperty(columnName).GetValue(marketValue); workSheet.Cells[nRows + 1, i] = value; sourceRange = workSheet.Cells[nRows, i]; if (nRows > 1) { sourceRange.Copy(Type.Missing); var hasFormula = sourceRange.HasFormula; Range destinationRange = workSheet.Cells[nRows + 1, i]; destinationRange.PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); if (hasFormula) { destinationRange.PasteSpecial(XlPasteType.xlPasteFormulas, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } } } excelApp.DisplayAlerts = false; workBook.Save(); workBook.Close(0); excelApp.Quit(); bRtnVal = true; } catch (Exception ex) { ApplicationLog.Instance.WriteException(ex); } finally { if (workSheet != null) { Marshal.ReleaseComObject(workSheet); } if (workBook != null) { Marshal.ReleaseComObject(workBook); } if (excelApp != null) { Marshal.ReleaseComObject(excelApp); } } return(bRtnVal); }