static void Main(string[] args) { string strConn = ConfigurationManager.ConnectionStrings["DBModel.Properties.Settings.StockDataConnectionString"].ConnectionString; List<SymbolInfo> symbolList = new List<SymbolInfo>(); //Load Symbols From CSV using (StreamReader sr = File.OpenText(@"F:\Projects\Git\StockDownloader\LoadSymbol\ETF_List.csv")) { var line = sr.ReadLine(); while(line != null) { var items = line.Split(new char[] { ',' }); if(items.Length ==4) { var si = new SymbolInfo(); si.NativeSymbol = items[0]; si.Name = items[1]; si.Exchange = items[2]; si.NativeCountry = items[3]; if(si.IsValid()) { symbolList.Add(si); } } line = sr.ReadLine(); } sr.Close(); } using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { foreach(var si in symbolList) { var stockSymbol = dbContext.StockSymbols.Where(s => s.Symbol == si.Symbol).SingleOrDefault(); if(stockSymbol == null) { stockSymbol = new StockSymbol() { Symbol = si.Symbol, StockName = si.Name, Country = si.Country, ETF = si.ETF }; dbContext.StockSymbols.InsertOnSubmit(stockSymbol); } else { stockSymbol.StockName = si.Name; stockSymbol.Country = si.Country; stockSymbol.ETF = si.ETF; } } dbContext.SubmitChanges(); } }
// If your activity returns a value, derive from CodeActivity<TResult> // and return the value from the Execute method. protected override void Execute(CodeActivityContext context) { string strSymbol = this.MySymbol.Get(context); string strConn = this.MyConnString.Get(context); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { StockPick lastPick = (from a in dbContext.StockPicks where a.Symbol == strSymbol && a.PickType == 3 orderby a.PickDate descending select a).FirstOrDefault(); DateTime lastDtPick = DateTime.Now.AddYears(-20); if (lastPick != null) { lastDtPick = lastPick.PickDate; } List<StockQuote> lstQuote = (from a in dbContext.StockQuotes where a.Symbol == strSymbol && a.TimeFrame == 2 && a.QuoteDate >= lastDtPick orderby a.QuoteDate select a).ToList(); int fallWeeks = 0; StockQuote prevQuote = null; int pastWeeks = 0; bool startChecking = false; foreach (StockQuote quote in lstQuote) { if (quote.CloseValue < quote.OpenValue) fallWeeks++; else fallWeeks = 0; if (fallWeeks >= 4) { pastWeeks = 0; startChecking = true; } if (pastWeeks <= 8 && startChecking) { pastWeeks++; } else { startChecking = false; } if (startChecking) { if (quote.CloseValue > quote.OpenValue) { string pickKey = string.Format("{0}_{1:yyyy-MM-dd}", quote.Symbol, quote.QuoteDate); if (quote.CloseValue > prevQuote.HighValue) { StockPick sp = new StockPick(); sp.PickDate = quote.QuoteDate; sp.PickType = 3; sp.Symbol = quote.Symbol; sp.PickKey = pickKey; dbContext.StockPicks.InsertOnSubmit(sp); dbContext.SubmitChanges(); startChecking = false; } } else prevQuote = quote; } } } }
// If your activity returns a value, derive from CodeActivity<TResult> // and return the value from the Execute method. protected override void Execute(CodeActivityContext context) { string strSymbol = this.MySymbol.Get(context); string strConn =this.MyConnString.Get(context); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { StockPeak lastPeak = (from sp in dbContext.StockPeaks where sp.Symbol == strSymbol && sp.TimeFrame == 1 orderby sp.PeakDate descending select sp).FirstOrDefault(); DateTime lastDtPeak = DateTime.Now.AddYears(-20); decimal lastPeakVal = 0; int lastPeakType = 0; if (lastPeak != null) { lastDtPeak = lastPeak.PeakDate; lastPeakType = lastPeak.PeakType; StockQuote sq = (from a in dbContext.StockQuotes where a.Symbol == strSymbol && a.QuoteDate == lastPeak.PeakDate && a.TimeFrame == lastPeak.TimeFrame select a).FirstOrDefault(); if (sq == null) return; if (lastPeakType < 0) lastPeakVal = sq.LowValue; else if (lastPeakType > 0) lastPeakVal = sq.HighValue; } List<StockQuote> lstQuote = (from sq in dbContext.StockQuotes where sq.Symbol == strSymbol && sq.TimeFrame == 1 && sq.QuoteDate >= lastDtPeak orderby sq.QuoteDate select sq).ToList(); for (int i = 0; i < lstQuote.Count; i++) { if(i>=PeakCount && (i+PeakCount)<=(lstQuote.Count-1)) { List<StockQuote> lstTmp = lstQuote.GetRange(i - PeakCount, PeakCount*2+1); decimal minLow = (from a in lstTmp orderby a.LowValue select a.LowValue).FirstOrDefault(); decimal maxHigh = (from a in lstTmp orderby a.HighValue descending select a.HighValue).FirstOrDefault(); if (lstQuote[i].HighValue >= maxHigh) { if (lastPeakType > 0) lastPeakType++; else lastPeakType = 1; StockPeak highPeak = new StockPeak(); highPeak.TimeFrame = lstQuote[i].TimeFrame; highPeak.Symbol = lstQuote[i].Symbol; highPeak.PeakDate = lstQuote[i].QuoteDate; highPeak.PeakType = lastPeakType; dbContext.StockPeaks.InsertOnSubmit(highPeak); dbContext.SubmitChanges(); } else if (lstQuote[i].LowValue <= minLow) { if (lastPeakType < 0) lastPeakType--; else lastPeakType = -1; StockPeak highPeak = new StockPeak(); highPeak.TimeFrame = lstQuote[i].TimeFrame; highPeak.Symbol = lstQuote[i].Symbol; highPeak.PeakDate = lstQuote[i].QuoteDate; highPeak.PeakType = lastPeakType; dbContext.StockPeaks.InsertOnSubmit(highPeak); dbContext.SubmitChanges(); } } } } }
// If your activity returns a value, derive from CodeActivity<TResult> // and return the value from the Execute method. protected override void Execute(CodeActivityContext context) { string symbol = context.GetValue(this.MySymbol); string strConn = this.MyConnString.Get(context); string requestURL = "http://finance.yahoo.com/d/quotes.csv?s=" + symbol + "&f=" + InfoTag.Symbol + InfoTag.Divident + InfoTag.MarketCap + InfoTag.PriceSales + InfoTag.PriceBooks + InfoTag.PE + InfoTag.PEG + InfoTag.DivedentPayDay + InfoTag.ShortRatio + InfoTag.DividentYield + InfoTag.AvgVolume; List<string> lstInfo = HttpLib.GetHttpRespsonse (requestURL); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { int iExist = (from q in dbContext.StockSymbols where q.Symbol == symbol select q).Count(); if(iExist<=0) throw new ApplicationException("Cannot find Symbol: " + symbol); foreach (string strInfo in lstInfo) { string[] items = strInfo.Split(','); if (items[(int)InfoOrder.Symbol].Trim().Replace("\"", "").ToUpper() == symbol.ToUpper()) { var infos = from i in dbContext.StockInformations where i.Symbol == symbol select i; List<StockInformation> lstStockInfo = infos.ToList<StockInformation>(); StockInformation stockInfo = new StockInformation(); if (lstStockInfo.Count == 1) stockInfo = lstStockInfo[0]; stockInfo.Symbol = symbol; #region 1.Parsing Divident if (Information.IsNumeric(items[(int)InfoOrder.Divident])) stockInfo.Divident = Convert.ToDecimal(items[(int)InfoOrder.Divident]); #endregion #region 2.Parsing MarketCap NumberDegree degree = NumberDegree.Unknown; string marketCap = items[(int)InfoOrder.MarketCap].Replace("\"", "").ToUpper(); if (marketCap.Contains("B")) { degree = NumberDegree.Billion; marketCap = marketCap.Replace("B", ""); } else if (marketCap.Contains("M")) { degree = NumberDegree.Million; marketCap = marketCap.Replace("M", ""); } else if (marketCap.Contains("K")) { degree = NumberDegree.Thousand; marketCap = marketCap.Replace("K", ""); } if (Information.IsNumeric(marketCap)) { stockInfo.MarKetCap = Convert.ToDecimal(marketCap); switch (degree) { case NumberDegree.Billion: stockInfo.MarKetCap *= 100000000; break; case NumberDegree.Million: stockInfo.MarKetCap *= 1000000; break; case NumberDegree.Thousand: stockInfo.MarKetCap *= 1000; break; } } #endregion #region 3.Parsing Price/Sales if (Information.IsNumeric(items[(int)InfoOrder.PriceSales])) stockInfo.PriceSales = Convert.ToDecimal(items[(int)InfoOrder.PriceSales]); #endregion #region 4.Parsing Price/Books if (Information.IsNumeric(items[(int)InfoOrder.PriceBooks])) stockInfo.PriceBooks = Convert.ToDecimal(items[(int)InfoOrder.PriceBooks]); #endregion #region 5.Parsing PE if (Information.IsNumeric(items[(int)InfoOrder.PE])) stockInfo.PE = Convert.ToDecimal(items[(int)InfoOrder.PE]); #endregion #region 6.Parsing PEG if (Information.IsNumeric(items[(int)InfoOrder.PEG])) stockInfo.PEG = Convert.ToDecimal(items[(int)InfoOrder.PEG]); #endregion #region 6.Parsing Dividents Payday string dividentDay = DateTime.Now.Year.ToString() + " " + items[(int)InfoOrder.DividentPayDay].Replace("\"", ""); if (Information.IsDate(dividentDay)) stockInfo.DivedentPayDay = Convert.ToDateTime(dividentDay); #endregion #region 7.Parsing Short Ratio if (Information.IsNumeric(items[(int)InfoOrder.ShortRatio])) stockInfo.ShortRatio = Convert.ToDecimal(items[(int)InfoOrder.ShortRatio]); #endregion #region 8.Parsing Divident Yield if (Information.IsNumeric(items[(int)InfoOrder.DividentYield])) stockInfo.DividentYield = Convert.ToDecimal(items[(int)InfoOrder.DividentYield]); #endregion #region 9.Parsing Divident Yield if (Information.IsNumeric(items[(int)InfoOrder.DividentYield])) stockInfo.DividentYield = Convert.ToDecimal(items[(int)InfoOrder.DividentYield]); #endregion #region 10.Parsing Average Volume if (Information.IsNumeric(items[(int)InfoOrder.AvgVolumn])) stockInfo.AvgVolume = Convert.ToInt32(items[(int)InfoOrder.AvgVolumn]); #endregion if (lstStockInfo.Count != 1) dbContext.StockInformations.InsertOnSubmit(stockInfo); dbContext.SubmitChanges(); } } } }
// If your activity returns a value, derive from CodeActivity<TResult> // and return the value from the Execute method. protected override void Execute(CodeActivityContext context) { string symbol = context.GetValue(this.MySymbol); string strConn = this.MyConnString.Get(context); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { dbContext.CommandTimeout = 120; StockSymbol sSymbol = (from s in dbContext.StockSymbols where s.Symbol == symbol select s).FirstOrDefault(); if(sSymbol == null) throw new ApplicationException("Cannot find Symbol: " + symbol); DateTime startDate = sSymbol.EndDate.HasValue ? sSymbol.EndDate.Value : new DateTime(1990,1,1); DateTime endDate = Helper.GetEndDate(DateTime.Now); while (startDate.DayOfWeek != DayOfWeek.Saturday) startDate = startDate.AddDays(-1); while (endDate.DayOfWeek != DayOfWeek.Saturday) endDate = endDate.AddDays(1); if (endDate > startDate) { var delQuotes = from s in dbContext.StockQuotes where s.Symbol == symbol && s.QuoteDate >= startDate && s.QuoteDate <= endDate select s; dbContext.StockQuotes.DeleteAllOnSubmit(delQuotes); dbContext.SubmitChanges(); //Dowload daily quotes first; then download weekly quotes for (int i = 0; i <= 1; i++) { TimeFrame timeFrame = TimeFrame.Day; if (i == 1) timeFrame = TimeFrame.Week; #region 1. Download Quotes string webAddress = "http://ichart.yahoo.com/table.csv?s=[%s]&a=[%m1]&b=[%d1]&c=[%y1]&d=[%m2]&e=[%d2]&f=[%y2]&g=[%Type]&ignore=.csv"; webAddress = webAddress.Replace("[%s]", symbol); webAddress = webAddress.Replace("[%m1]", (startDate.Month - 1).ToString()); webAddress = webAddress.Replace("[%d1]", startDate.Day.ToString()); webAddress = webAddress.Replace("[%y1]", startDate.Year.ToString()); webAddress = webAddress.Replace("[%m2]", (endDate.Month - 1).ToString()); webAddress = webAddress.Replace("[%d2]", endDate.Day.ToString()); webAddress = webAddress.Replace("[%y2]", endDate.Year.ToString()); string dateType = string.Empty; if (timeFrame == TimeFrame.Day) dateType = "d"; else if (timeFrame == TimeFrame.Week) dateType = "w"; webAddress = webAddress.Replace("[%Type]", dateType); List<string> lstQuote = HttpLib.GetHttpRespsonse(webAddress); if (lstQuote.Count > 0) { foreach (string strQuote in lstQuote) { string[] items = strQuote.Split(','); if (Microsoft.VisualBasic.Information.IsDate(items[0])) { StockQuote stockQuote = new StockQuote(); decimal close = Convert.ToDecimal(items[(int)QuoteOrder.ClosePrice]); decimal adjustClose = Convert.ToDecimal(items[(int)QuoteOrder.AdjustedClose]); decimal ratio = adjustClose / close; stockQuote.Symbol = symbol; stockQuote.QuoteDate = Convert.ToDateTime(items[(int)QuoteOrder.PriceDate]); stockQuote.OpenValue = Convert.ToDecimal(items[(int)QuoteOrder.OpenPrice]) * ratio; stockQuote.CloseValue = (Decimal)adjustClose; stockQuote.LowValue = Convert.ToDecimal(items[(int)QuoteOrder.LowPrice]) * ratio; stockQuote.HighValue = Convert.ToDecimal(items[(int)QuoteOrder.HighPrice]) * ratio; stockQuote.Volume = Convert.ToInt64 (items[(int)QuoteOrder.Volume]); stockQuote.TimeFrame = (short)timeFrame; dbContext.StockQuotes.InsertOnSubmit(stockQuote); } } dbContext.SubmitChanges(); } #endregion #region 2. Update Symbol's Start/End Date if (timeFrame == TimeFrame.Day) { StockSymbol symbolItem = (from s in dbContext.StockSymbols where s.Symbol == symbol select s).SingleOrDefault(); DateTime firstQuoteDate = (from q in dbContext.StockQuotes where q.Symbol == symbol && q.TimeFrame == (short)timeFrame orderby q.QuoteDate select q.QuoteDate).FirstOrDefault(); DateTime lastQuoteDate = (from q in dbContext.StockQuotes where q.Symbol == symbol && q.TimeFrame == (short)timeFrame orderby q.QuoteDate descending select q.QuoteDate).FirstOrDefault(); if (symbolItem != null) { try { symbolItem.StartDate = firstQuoteDate; symbolItem.EndDate = lastQuoteDate; dbContext.SubmitChanges(); } catch (Exception e) { } } } #endregion } } } }
// If your activity returns a value, derive from CodeActivity<TResult> // and return the value from the Execute method. protected override void Execute(CodeActivityContext context) { string strSymbol = this.MySymbol.Get(context); string strConn = this.MyConnString.Get(context); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { StockPick lastPick = (from a in dbContext.StockPicks where a.Symbol == strSymbol && a.PickType == 2 orderby a.PickDate descending select a).FirstOrDefault(); DateTime lastDtPick = DateTime.Now.AddYears(-20); if (lastPick != null) { lastDtPick = lastPick.PickDate; } List<StockPeak> lstPeaks = (from a in dbContext.StockPeaks where a.Symbol == strSymbol && a.TimeFrame == 1 && a.PeakDate >= lastDtPick.AddMonths(-6) && a.PeakType >0 orderby a.PeakDate select a).ToList(); List<StockQuote> lstQuote = (from a in dbContext.StockQuotes where a.Symbol == strSymbol && a.TimeFrame == 1 && a.QuoteDate >= lastDtPick orderby a.QuoteDate select a).ToList(); string orgPickKey = ""; foreach (StockQuote sq in lstQuote) { List<StockPeak> lstRcntPk = (from p in lstPeaks where p.PeakDate <= sq.QuoteDate && p.PeakDate >= sq.QuoteDate.AddMonths(-6) && p.PeakType >0 orderby p.PeakDate descending select p).ToList(); StockQuote prevQuote = (from q in lstQuote where q.QuoteDate < sq.QuoteDate orderby q.QuoteDate descending select q).FirstOrDefault(); if (prevQuote != null) { decimal maxPeak = 0; foreach (StockPeak sPeak in lstRcntPk) { StockQuote pQuote = (from q in lstQuote where q.TimeFrame == sPeak.TimeFrame && q.QuoteDate == sPeak.PeakDate select q).FirstOrDefault(); if (pQuote != null) { if (pQuote.HighValue > maxPeak) maxPeak = pQuote.HighValue; } } if (prevQuote.CloseValue < maxPeak && sq.CloseValue > maxPeak) { string pickKey = string.Format("{0}_{1:yyyy-MM-dd}_2", sq.Symbol,sq.QuoteDate ); if (pickKey != orgPickKey) { int existFlag = (from p in dbContext.StockPicks where p.PickKey == pickKey select p.PickKey).Count(); if (existFlag <= 0) { StockPick sp = new StockPick(); sp.PickDate = sq.QuoteDate; sp.PickType = 2; sp.Symbol = sq.Symbol; sp.PickKey = pickKey; dbContext.StockPicks.InsertOnSubmit(sp); dbContext.SubmitChanges(); } //Console.WriteLine(string.Format("Pick {0:yyyy-MM-dd}", sq.QuoteDate)); orgPickKey = pickKey; } } } } } }
// If your activity returns a value, derive from CodeActivity<TResult> // and return the value from the Execute method. protected override void Execute(CodeActivityContext context) { string strSymbol = this.MySymbol.Get(context); string strConn = this.MyConnString.Get(context); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { StockPick lastPick = (from a in dbContext.StockPicks where a.Symbol == strSymbol && a.PickType == 1 orderby a.PickDate descending select a).FirstOrDefault(); DateTime lastDtPick = DateTime.Now.AddYears(-20); if (lastPick != null) { lastDtPick = lastPick.PickDate; } List<StockPeak> lstPeaks = (from a in dbContext.StockPeaks where a.Symbol == strSymbol && a.TimeFrame == 1 && a.PeakDate >= lastDtPick.AddMonths(-6) orderby a.PeakDate select a).ToList(); List<StockQuote> lstQuote = (from a in dbContext.StockQuotes where a.Symbol == strSymbol && a.TimeFrame == 1 && a.QuoteDate >= lastDtPick orderby a.QuoteDate select a).ToList(); string orgPickKey = ""; foreach (StockQuote sq in lstQuote) { List<StockPeak> lstRcntPk = (from p in lstPeaks where p.PeakDate <= sq.QuoteDate && p.PeakDate >= sq.QuoteDate.AddDays(-45) orderby p.PeakDate descending select p).ToList(); if (lstRcntPk.Count >= 3) { if (lstRcntPk[0].PeakType < 0) { decimal p1 = (from q in lstQuote where q.QuoteDate == lstRcntPk[0].PeakDate select q.LowValue).FirstOrDefault(); decimal p2 = 0; decimal p3 = 0; for (int i = 1; i < lstRcntPk.Count; i++) { if (lstRcntPk[i].PeakType < 0 && p3==0) { p3 = (from q in lstQuote where q.QuoteDate == lstRcntPk[i].PeakDate select q.LowValue).FirstOrDefault(); } if (lstRcntPk[i].PeakType >0 && p2 == 0) { p2 = (from q in lstQuote where q.QuoteDate == lstRcntPk[i].PeakDate select q.HighValue).FirstOrDefault(); } if (p2 != 0 && p3 != 0) break; } if (p2!=0 && p3 !=0) { if (p1 <= p3 && sq.CloseValue > p2) { string pickKey = string.Format("{0}_{1:yyyy-MM-dd}_{2:yyyy-MM-dd}", sq.Symbol, lstRcntPk[0].PeakDate, lstRcntPk[1].PeakDate); if (pickKey != orgPickKey) { int existFlag = (from p in dbContext.StockPicks where p.PickKey == pickKey select p.PickKey).Count(); if (existFlag <= 0) { StockPick sp = new StockPick(); sp.PickDate = sq.QuoteDate; sp.PickType = 1; sp.Symbol = sq.Symbol; sp.PickKey = pickKey; dbContext.StockPicks.InsertOnSubmit(sp); dbContext.SubmitChanges(); } //Console.WriteLine(string.Format("Pick {0:yyyy-MM-dd}", sq.QuoteDate)); orgPickKey = pickKey; } } } } } } } }
protected override void Execute(CodeActivityContext context) { string symbol = context.GetValue(this.MySymbol); string strConn = this.MyConnString.Get(context); using (StockDBDataContext dbContext = new StockDBDataContext(strConn)) { TradingGame_StockPerformance tspLatest = (from tsp in dbContext.TradingGame_StockPerformances where tsp.Symbol == symbol orderby tsp.StartDate descending select tsp).FirstOrDefault(); DateTime dtStartDate = new DateTime(1990, 1, 1); DateTime dtEndDate = DateTime.Now.AddMonths(-14); if (tspLatest != null) { dtStartDate = tspLatest.StartDate.AddMonths(1); } else { DateTime? dbStartTime = (from s in dbContext.StockSymbols where s.Symbol == symbol select s.StartDate).FirstOrDefault(); if (dbStartTime.HasValue == false) dtStartDate = DateTime.Now.AddYears(1); } while (dtStartDate < dtEndDate) { TradingGame_StockPerformance objSP = new TradingGame_StockPerformance(); //Get Quote of Strat Date StockQuote quote = (from q in dbContext.StockQuotes where q.Symbol == symbol && q.TimeFrame == (short)TimeFrame.Day && q.QuoteDate >= dtStartDate && q.QuoteDate <= dtStartDate.AddDays(14) orderby q.QuoteDate select q).FirstOrDefault(); if (quote != null) { objSP.Symbol = quote.Symbol; objSP.StartDate = quote.QuoteDate; //Get Performance of 3 Month StockQuote quote_3M = (from q in dbContext.StockQuotes where q.Symbol == symbol && q.TimeFrame == (short)TimeFrame.Day && q.QuoteDate >= dtStartDate.AddMonths(3) && q.QuoteDate <= dtStartDate.AddMonths(3).AddDays(14) orderby q.QuoteDate select q).FirstOrDefault(); if (quote_3M != null) { objSP.ThreeMonth = (double)(quote_3M.CloseValue / quote.CloseValue); } //Get Performance of 6 Month StockQuote quote_6M = (from q in dbContext.StockQuotes where q.Symbol == symbol && q.TimeFrame == (short)TimeFrame.Day && q.QuoteDate >= dtStartDate.AddMonths(6) && q.QuoteDate <= dtStartDate.AddMonths(6).AddDays(14) orderby q.QuoteDate select q).FirstOrDefault(); if (quote_6M != null) { objSP.SixMonth = (double)(quote_6M.CloseValue / quote.CloseValue); } //Get Performance of 12 Month StockQuote quote_12M = (from q in dbContext.StockQuotes where q.Symbol == symbol && q.TimeFrame == (short)TimeFrame.Day && q.QuoteDate >= dtStartDate.AddMonths(12) && q.QuoteDate <= dtStartDate.AddMonths(12).AddDays(14) orderby q.QuoteDate select q).FirstOrDefault(); if (quote_12M != null) { objSP.TwelveMonth = (double)(quote_12M.CloseValue / quote.CloseValue); } //Insert Performance Date if (objSP.Symbol.Length > 0) { dbContext.TradingGame_StockPerformances.InsertOnSubmit(objSP); dbContext.SubmitChanges(); } } if (dtStartDate.Month == 12) { dtStartDate = new DateTime(dtStartDate.Year + 1, 1, 1); } else { dtStartDate = new DateTime(dtStartDate.Year, dtStartDate.Month + 1, 1); } } } }