|
1607 0 |
|
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace Macro_Correlation_Divergence_Arbitrage {
class Program {
static int LastDay = 0;
//static DataSet Correlations;
static List<Quote> Quotes;
static List<string> Emails = new List<string>();
static void Main(string[] args) {
Quotes = new List<Quote>();
//string test = Console.ReadLine();
//Quotes.Add(new Quote(test));
Stopwatch timer = new Stopwatch();
Stopwatch correlationTimer = new Stopwatch();
DataLoader dl = new DataLoader();
// Correlations = dl.LoadData();
//for (int i = 1; i < Correlations.Tables[0].Columns.Count; i++) {
// Quotes.Add(new Quote(Correlations.Tables[0].Columns[i].ColumnName));
//}
//DictionarySaveCorrelations();
MySqlDataReader reader = DataBaseHandler.GetData("SELECT DISTINCT StockFrom FROM Correlation");
while (reader.Read()) {
Quotes.Add(new Quote((string)reader["StockFrom"]));
}
Quotes = YahooStockEngine.Fetch(Quotes);
foreach (Quote q in Quotes) {
Console.WriteLine(q.Symbol);
Console.WriteLine(q.Name);
Console.WriteLine(q.LastTradePrice);
Console.WriteLine(q.Ask);
}
correlationTimer.Start();
timer.Start();
while (true) {
if (DateTime.Now.Day != LastDay) {
LastDay = DateTime.Now.Day;
DataBaseHandler.SetData("DELETE FROM PricingHistory WHERE Time < '" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0) + "'");
}
if (correlationTimer.Elapsed.Minutes > 30) {
correlationTimer.Stop();
foreach (Quote q in Quotes) {
CheckVsData(q.Symbol);
}
correlationTimer.Reset();
correlationTimer.Start();
}
if (timer.Elapsed.TotalSeconds >= 10) {
timer.Stop();
timer.Reset();
Quotes = YahooStockEngine.Fetch(Quotes);
foreach (Quote q in Quotes) {
Console.WriteLine(q.Name + " ");
if (q.Ask != null) {
Console.Write(q.Ask);
DataBaseHandler.SetData("INSERT INTO PricingHistory(Name, Price) Values('" + q.Symbol + "', " + q.Ask + ")");
} else if (q.LastTradePrice != null) {
Console.Write(q.LastTradePrice);
DataBaseHandler.SetData("INSERT INTO PricingHistory(Name, Price) Values('" + q.Symbol + "', " + q.LastTradePrice + ")");
} else {
SendEmail("Something went wrong on line 65, with pricing", false);
}
}
timer.Start();
}
if (Emails.Count > 1) {
string info = "";
foreach (string s in Emails) {
info += string.Format(" {0} \n ", s);
}
SendEmail(info);
Emails.Clear();
}
}
}
//private static void DictionarySaveCorrelations() {
// for (int i = 1; i < Correlations.Tables[0].Columns.Count; i++) {
// for (int j = 0; j < Correlations.Tables[0].Rows.Count; j++) {
// DataBaseHandler.SetData(string.Format("INSERT INTO Correlation(StockFrom, StockTo, HistoricCorrelation) Values('{0}', '{1}', {2})", Correlations.Tables[0].Columns[i].ColumnName, Correlations.Tables[0].Rows[j][Correlations.Tables[0].Columns[0].ColumnName], Correlations.Tables[0].Rows[j][Correlations.Tables[0].Columns[i].ColumnName]));
// }
// }
//}
static void CheckVsData(string Name) {
foreach (Quote q in Quotes) {
ComputeCoeff(Name, q.Symbol);
}
foreach (Quote q in Quotes) {
MySqlDataReader reader = DataBaseHandler.GetData("SELECT StockFrom, StockTo, HistoricCorrelation, CurrentCorrelation FROM Correlation WHERE (HistoricCorrelation > 0 AND CurrentCorrelation < 0) OR (HistoricCorrelation < 0 AND CurrentCorrelation > 0)");
while (reader.Read()) {
Emails.Add(string.Format("{0} had deviated from its correlation with {1} \n It use to be at {2} and it is now at {3}", (string)reader["StockFrom"], (string)reader["StockTo"], Math.Round((double)reader["HistoricCorrelation"], 5), Math.Round((double)reader["CurrentCorrelation"], 5)));
}
}
}
static void ComputeCoeff(string From, string To) {
//Equation = Sum of (x-x bar)(y - y bar) / Sqaure root of Sum of (x - x bar)^2 Sum Of (y - y bar)^2
int FromCount = DataBaseHandler.GetCount("SELECT COUNT(Time) FROM PricingHistory Where Name = '" + From + "'");
int ToCount = DataBaseHandler.GetCount("SELECT COUNT(Time) FROM PricingHistory Where Name = '" + To + "'");
if (FromCount != ToCount) {
throw new ArgumentException("values must be the same length");
}
var FromAVG = DataBaseHandler.GetCount("SELECT AVG(Price) FROM PricingHistory Where Name = '" + From + "' AND Time > '" + DateTime.Now.AddMinutes(-31));
var ToAVG = DataBaseHandler.GetCount("SELECT AVG(Price) FROM PricingHistory Where Name = '" + To + "' AND Time > '" + DateTime.Now.AddMinutes(-31));
List<double> Value1 = new List<double>();
List<double> Value2 = new List<double>();
MySqlDataReader reader = DataBaseHandler.GetData("SELECT (Name, Price) FROM PricingHistory Where Name = '" + To + "' OR WHERE Name = '" + From + "'");
while (reader.Read()) {
if ((string)reader["Name"] == From) {
Value1.Add((double)reader["Price"]);
} else {
Value2.Add((double)reader["Price"]);
}
}
double SumOfXY = 0;
for (int i = 0; i < Value1.Count; i++) {
SumOfXY += Value1[i] * Value2[i];
}
double XSum = DataBaseHandler.GetCountDouble("SELECT SUM(Price) FROM PricingHistory Where Name = '" + From + "'");
double YSum = DataBaseHandler.GetCountDouble("SELECT SUM(Price) FROM PricingHistory Where Name = '" + To + "'");
double TopRightOfEquation = (XSum * YSum) / FromCount;
double XSumSquared = DataBaseHandler.GetCountDouble("SELECT SUM(Price * Price) FROM PricingHistory Where Name = '" + From + "'");
double YSumSquared = DataBaseHandler.GetCountDouble("SELECT SUM(Price * Price) FROM PricingHistory Where Name = '" + To + "'");
double Sxx = XSumSquared - ((XSum * XSum) / FromCount);
double Syy = YSumSquared - ((YSum * YSum) / FromCount);
double result = (SumOfXY - TopRightOfEquation) / (Math.Sqrt(Sxx * Syy));
DataBaseHandler.SetData("UPDATE Correlation SET CurrentCorrelation = " + result + " WHERE StockFrom = '" + From + "' AND StockTo = '" + To + "'");
}
public static void SendEmail(string Info, bool AndCharlie = true) {
MailMessage mail = new MailMessage("sammyben.richards@gmail.com", "sammyrichards@me.com");
SmtpClient client = new SmtpClient();
client.Port = 25;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Host = "smtp.gmail.com";
mail.Subject = "We've had some Deviation";
mail.Body = Info;
client.EnableSsl = true;
System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential("sammyben.richards@gmail.com", "Lucent101");
client.Credentials = basicAuthenticationInfo;
client.Send(mail);
if (AndCharlie) {
mail = new MailMessage("sammyben.richards@gmail.com", "massycollier@gmail.com");
client = new SmtpClient();
client.Port = 25;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Host = "smtp.gmail.com";
mail.Subject = "We've had some Deviation";
mail.Body = Info;
client.EnableSsl = true;
basicAuthenticationInfo = new System.Net.NetworkCredential("sammyben.richards@gmail.com", "Lucent101");
client.Credentials = basicAuthenticationInfo;
client.Send(mail);
}
}
}
public class TimePeriod {
public Quote quote;
public decimal? StartPrice;
public List<decimal?> Prices = new List<decimal?>();
public decimal? EndPrice { get { return Prices[Prices.Count - 1]; } }
}
}
|
using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Macro_Correlation_Divergence_Arbitrage {
class DataLoader {
public DataSet LoadData() {
FileStream stream = File.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Stocks.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
excelReader.Close();
return result;
}
}
}
|
using System;
using MySql.Data.MySqlClient;
using System.Data;
namespace Macro_Correlation_Divergence_Arbitrage {
class DataBaseHandler {
static string myConnectionString = "server=localhost;database=Correlations;uid=Sam;pwd=230999;";
public static MySqlConnection sqlCon = new MySqlConnection(myConnectionString);
public static void StartServer() {
OpenConnection();
}
public static MySqlDataReader GetData(string command) {
ReadyConnection();
MySqlCommand com = new MySqlCommand(command, sqlCon);
MySqlDataReader reader;
//for (int i = 0; i < 10; i++) {
//try {
reader = com.ExecuteReader();
return reader;
//}
//catch {
// if (i == 9) {
// try {
// sqlCon.Close();
// } catch { }
// OpenConnection();
// i = 0;
// }
//}
//}
}
public static void SetData(string command) {
ReadyConnection();
MySqlCommand com = new MySqlCommand(command, sqlCon);
//for (int i = 0; i < 10; i++) {
//try {
com.ExecuteNonQuery();
// } catch {
// if (i == 9) {
// try {
// sqlCon.Close();
// } catch { }
// OpenConnection();
// i = 0;
// }
// }
//}
}
public static int GetCount(string command) {
ReadyConnection();
MySqlCommand com = new MySqlCommand(command, sqlCon);
//for (int i = 0; i < 10; i++) {
//try {
string t = com.ExecuteScalar().ToString();
if (t.Length == 0) {
return 0;
} else {
return int.Parse(t);
}
// } catch {
// if (i == 9) {
// try {
// sqlCon.Close();
// } catch { }
// OpenConnection();
// i = 0;
// }
// }
//}
}
public static double GetCountDouble(string command) {
ReadyConnection();
MySqlCommand com = new MySqlCommand(command, sqlCon);
//for (int i = 0; i < 10; i++) {
//try {
string t = com.ExecuteScalar().ToString();
if (t.Length == 0) {
return 0;
} else {
return double.Parse(t);
}
// } catch {
// if (i == 9) {
// try {
// sqlCon.Close();
// } catch { }
// OpenConnection();
// i = 0;
// }
// }
//}
}
static void ReadyConnection() {
try {
sqlCon.Close();
} catch { }
StartServer();
}
private static void OpenConnection() {
for (int i = 0; i < 10; i++) {
try {
sqlCon.Open();
break;
} catch {
if (i == 9) {
throw new Exception("Failed to connect to database");
}
}
}
}
}
}
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Macro_Correlation_Divergence_Arbitrage {
public class Quote {
private string symbol;
private decimal? averageDailyVolume;
private decimal? bid;
private decimal? ask;
private decimal? bookValue;
private decimal? changePercent;
private decimal? change;
private decimal? dividendShare;
private DateTime? lastTradeDate;
private decimal? earningsShare;
private decimal? epsEstimateCurrentYear;
private decimal? epsEstimateNextYear;
private decimal? epsEstimateNextQuarter;
private decimal? dailyLow;
private decimal? dailyHigh;
private decimal? yearlyLow;
private decimal? yearlyHigh;
private decimal? marketCapitalization;
private decimal? ebitda;
private decimal? changeFromYearLow;
private decimal? percentChangeFromYearLow;
private decimal? changeFromYearHigh;
private decimal? percentChangeFromYearHigh;
private decimal? lastTradePrice;
private decimal? fiftyDayMovingAverage;
private decimal? twoHunderedDayMovingAverage;
private decimal? changeFromTwoHundredDayMovingAverage;
private decimal? percentChangeFromFiftyDayMovingAverage;
private string name;
private decimal? open;
private decimal? previousClose;
private decimal? changeInPercent;
private decimal? priceSales;
private decimal? priceBook;
private DateTime? exDividendDate;
private decimal? pegRatio;
private decimal? priceEpsEstimateCurrentYear;
private decimal? priceEpsEstimateNextYear;
private decimal? shortRatio;
private decimal? oneYearPriceTarget;
private decimal? dividendYield;
private DateTime? dividendPayDate;
private decimal? percentChangeFromTwoHundredDayMovingAverage;
private decimal? peRatio;
private decimal? volume;
private string stockExchange;
private DateTime lastUpdate;
public DateTime LastUpdate {
get { return lastUpdate; }
set {
lastUpdate = value;
}
}
public string StockExchange {
get { return stockExchange; }
set {
stockExchange = value;
}
}
public decimal? Volume {
get { return volume; }
set {
volume = value;
}
}
public decimal? PeRatio {
get { return peRatio; }
set {
peRatio = value;
}
}
public decimal? PercentChangeFromTwoHundredDayMovingAverage {
get { return percentChangeFromTwoHundredDayMovingAverage; }
set {
percentChangeFromTwoHundredDayMovingAverage = value;
}
}
public Quote(string ticker) {
symbol = ticker;
}
public DateTime? DividendPayDate {
get { return dividendPayDate; }
set {
dividendPayDate = value;
}
}
public decimal? DividendYield {
get { return dividendYield; }
set {
dividendYield = value;
}
}
public decimal? OneYearPriceTarget {
get { return oneYearPriceTarget; }
set {
oneYearPriceTarget = value;
}
}
public decimal? ShortRatio {
get { return shortRatio; }
set {
shortRatio = value;
}
}
public decimal? PriceEpsEstimateNextYear {
get { return priceEpsEstimateNextYear; }
set {
priceEpsEstimateNextYear = value;
}
}
public decimal? PriceEpsEstimateCurrentYear {
get { return priceEpsEstimateCurrentYear; }
set {
priceEpsEstimateCurrentYear = value;
}
}
public decimal? PegRatio {
get { return pegRatio; }
set {
pegRatio = value;
}
}
public DateTime? ExDividendDate {
get { return exDividendDate; }
set {
exDividendDate = value;
}
}
public decimal? PriceBook {
get { return priceBook; }
set {
priceBook = value;
}
}
public decimal? PriceSales {
get { return priceSales; }
set {
priceSales = value;
}
}
public decimal? ChangeInPercent {
get { return changeInPercent; }
set {
changeInPercent = value;
}
}
public decimal? PreviousClose {
get { return previousClose; }
set {
previousClose = value;
}
}
public decimal? Open {
get { return open; }
set {
open = value;
}
}
public string Name {
get { return name; }
set {
name = value;
}
}
public decimal? PercentChangeFromFiftyDayMovingAverage {
get { return percentChangeFromFiftyDayMovingAverage; }
set {
percentChangeFromFiftyDayMovingAverage = value;
}
}
public decimal? ChangeFromTwoHundredDayMovingAverage {
get { return changeFromTwoHundredDayMovingAverage; }
set {
changeFromTwoHundredDayMovingAverage = value;
}
}
public decimal? TwoHunderedDayMovingAverage {
get { return twoHunderedDayMovingAverage; }
set {
twoHunderedDayMovingAverage = value;
}
}
public decimal? FiftyDayMovingAverage {
get { return fiftyDayMovingAverage; }
set {
fiftyDayMovingAverage = value;
}
}
public decimal? LastTradePrice {
get { return lastTradePrice; }
set {
lastTradePrice = value;
}
}
public decimal? PercentChangeFromYearHigh {
get { return percentChangeFromYearHigh; }
set {
percentChangeFromYearHigh = value;
}
}
public decimal? ChangeFromYearHigh {
get { return changeFromYearHigh; }
set {
changeFromYearHigh = value;
}
}
public decimal? PercentChangeFromYearLow {
get { return percentChangeFromYearLow; }
set {
percentChangeFromYearLow = value;
}
}
public decimal? ChangeFromYearLow {
get { return changeFromYearLow; }
set {
changeFromYearLow = value;
}
}
public decimal? Ebitda {
get { return ebitda; }
set {
ebitda = value;
}
}
public decimal? MarketCapitalization {
get { return marketCapitalization; }
set {
marketCapitalization = value;
}
}
public decimal? YearlyHigh {
get { return yearlyHigh; }
set {
yearlyHigh = value;
}
}
public decimal? YearlyLow {
get { return yearlyLow; }
set {
yearlyLow = value;
}
}
public decimal? DailyHigh {
get { return dailyHigh; }
set {
dailyHigh = value;
}
}
public decimal? DailyLow {
get { return dailyLow; }
set {
dailyLow = value;
}
}
public decimal? EpsEstimateNextQuarter {
get { return epsEstimateNextQuarter; }
set {
epsEstimateNextQuarter = value;
}
}
public decimal? EpsEstimateNextYear {
get { return epsEstimateNextYear; }
set {
epsEstimateNextYear = value;
}
}
public decimal? EpsEstimateCurrentYear {
get { return epsEstimateCurrentYear; }
set {
epsEstimateCurrentYear = value;
}
}
public decimal? EarningsShare {
get { return earningsShare; }
set {
earningsShare = value;
}
}
public DateTime? LastTradeDate {
get { return lastTradeDate; }
set {
lastTradeDate = value;
}
}
public decimal? DividendShare {
get { return dividendShare; }
set {
dividendShare = value;
}
}
public decimal? Change {
get { return change; }
set {
change = value;
}
}
public decimal? ChangePercent {
get { return changePercent; }
set {
changePercent = value;
}
}
public decimal? BookValue {
get { return bookValue; }
set {
bookValue = value;
}
}
public decimal? Ask {
get { return ask; }
set {
ask = value;
}
}
public decimal? Bid {
get { return bid; }
set {
bid = value;
}
}
public decimal? AverageDailyVolume {
get { return averageDailyVolume; }
set {
averageDailyVolume = value;
}
}
public string Symbol {
get { return symbol; }
set {
symbol = value;
}
}
}
}
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
namespace Macro_Correlation_Divergence_Arbitrage {
public class YahooStockEngine {
private const string BASE_URL = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20({0})&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";
public static List<Quote> Fetch(List<Quote> quotes) {
string symbolList = String.Join("%2C", quotes.Select(w => "%22" + w.Symbol + "%22").ToArray());
string url = string.Format(BASE_URL, symbolList);
XDocument doc = XDocument.Load(url);
Parse(ref quotes, doc);
return quotes;
}
private static void Parse(ref List<Quote> quotes, XDocument doc) {
XElement results = doc.Root.Element("results");
foreach (Quote quote in quotes) {
try {
XElement q = results.Elements("quote").First(w => w.Attribute("symbol").Value == quote.Symbol);
quote.Ask = GetDecimal(q.Element("Ask").Value);
quote.Bid = GetDecimal(q.Element("Bid").Value);
quote.AverageDailyVolume = GetDecimal(q.Element("AverageDailyVolume").Value);
quote.BookValue = GetDecimal(q.Element("BookValue").Value);
quote.Change = GetDecimal(q.Element("Change").Value);
quote.DividendShare = GetDecimal(q.Element("DividendShare").Value);
quote.LastTradeDate = GetDateTime(q.Element("LastTradeDate") + " " + q.Element("LastTradeTime").Value);
quote.EarningsShare = GetDecimal(q.Element("EarningsShare").Value);
quote.EpsEstimateCurrentYear = GetDecimal(q.Element("EPSEstimateCurrentYear").Value);
quote.EpsEstimateNextYear = GetDecimal(q.Element("EPSEstimateNextYear").Value);
quote.EpsEstimateNextQuarter = GetDecimal(q.Element("EPSEstimateNextQuarter").Value);
quote.DailyLow = GetDecimal(q.Element("DaysLow").Value);
quote.DailyHigh = GetDecimal(q.Element("DaysHigh").Value);
quote.YearlyLow = GetDecimal(q.Element("YearLow").Value);
quote.YearlyHigh = GetDecimal(q.Element("YearHigh").Value);
quote.MarketCapitalization = GetDecimal(q.Element("MarketCapitalization").Value);
quote.Ebitda = GetDecimal(q.Element("EBITDA").Value);
quote.ChangeFromYearLow = GetDecimal(q.Element("ChangeFromYearLow").Value);
quote.PercentChangeFromYearLow = GetDecimal(q.Element("PercentChangeFromYearLow").Value);
quote.ChangeFromYearHigh = GetDecimal(q.Element("ChangeFromYearHigh").Value);
quote.LastTradePrice = GetDecimal(q.Element("LastTradePriceOnly").Value);
quote.PercentChangeFromYearHigh = GetDecimal(q.Element("PercebtChangeFromYearHigh").Value); //missspelling in yahoo for field name
quote.FiftyDayMovingAverage = GetDecimal(q.Element("FiftydayMovingAverage").Value);
quote.TwoHunderedDayMovingAverage = GetDecimal(q.Element("TwoHundreddayMovingAverage").Value);
quote.ChangeFromTwoHundredDayMovingAverage = GetDecimal(q.Element("ChangeFromTwoHundreddayMovingAverage").Value);
quote.PercentChangeFromTwoHundredDayMovingAverage = GetDecimal(q.Element("PercentChangeFromTwoHundreddayMovingAverage").Value);
quote.PercentChangeFromFiftyDayMovingAverage = GetDecimal(q.Element("PercentChangeFromFiftydayMovingAverage").Value);
quote.Name = q.Element("Name").Value;
quote.Open = GetDecimal(q.Element("Open").Value);
quote.PreviousClose = GetDecimal(q.Element("PreviousClose").Value);
quote.ChangeInPercent = GetDecimal(q.Element("ChangeinPercent").Value);
quote.PriceSales = GetDecimal(q.Element("PriceSales").Value);
quote.PriceBook = GetDecimal(q.Element("PriceBook").Value);
quote.ExDividendDate = GetDateTime(q.Element("ExDividendDate").Value);
quote.PeRatio = GetDecimal(q.Element("PERatio").Value);
quote.DividendPayDate = GetDateTime(q.Element("DividendPayDate").Value);
quote.PegRatio = GetDecimal(q.Element("PEGRatio").Value);
quote.PriceEpsEstimateCurrentYear = GetDecimal(q.Element("PriceEPSEstimateCurrentYear").Value);
quote.PriceEpsEstimateNextYear = GetDecimal(q.Element("PriceEPSEstimateNextYear").Value);
quote.ShortRatio = GetDecimal(q.Element("ShortRatio").Value);
quote.OneYearPriceTarget = GetDecimal(q.Element("OneyrTargetPrice").Value);
quote.Volume = GetDecimal(q.Element("Volume").Value);
quote.StockExchange = q.Element("StockExchange").Value;
quote.LastUpdate = DateTime.Now;
} catch {
//Program.SendEmail("Error on line 73 of Yahoo Stock Engine");
}
}
}
private static decimal? GetDecimal(string input) {
if (input == null) return null;
input = input.Replace("%", "");
decimal value;
if (Decimal.TryParse(input, out value)) return value;
return null;
}
private static DateTime? GetDateTime(string input) {
if (input == null) return null;
DateTime value;
if (DateTime.TryParse(input, out value)) return value;
return null;
}
}
}