public static void addEverglades()
{
using (var context = new qpcptfaw())
{
if (context.Everglades.Count()==0)
{
EvergladesDB e = new EvergladesDB { name = "Everglades"};
context.Assets.Add(e);
context.SaveChanges();
AssetDB.assetCounter();
}
}
}
public static void storeEvergladesPrice(DateTime date, double price)
{
using (var context = new qpcptfaw())
{
//récupère l'id d'Everglades
int id = Access.GetIdEverglades();
var ev = from e in context.Prices
where e.AssetDBId == id && e.date == date
select e;
// si la date existe déjà dans la table des prix on la remplace
if (ev.Count()==1) //list_pair_db.Contains(new KeyValuePair<int, DateTime>(id, date)))
{
// on vérifie que la valeur de prix est différente
// si identiques on return (rien à faire)
if (ev.First().price == price && ev.First().priceEur == price)
{
return;
}
// sinon on remplace
ev.First().price = price;
ev.First().priceEur = price;
context.SaveChanges();
return;
}
else if (ev.Count()==0)
{
// sinon on l'ajoute
Price p = new Price { AssetDBId = id, date = date, price = price, priceEur =price };
context.Prices.Add(p);
context.SaveChanges();
return;
}
else
{
throw new Exception("Problème dans la BD.");
}
}
}
public static void storePortfolioValue(DateTime date, double value)
{
using (var context = new qpcptfaw())
{
var ev = from e in context.Portfolio
where e.date == date
select e;
// si la date existe déjà dans la table des prix on la remplace
if (ev.Count() == 1) //list_pair_db.Contains(new KeyValuePair<int, DateTime>(id, date)))
{
// on vérifie que la valeur de prix est différente
// si identiques on return (rien à faire)
if (ev.First().value == value)
{
return;
}
// sinon on remplace
ev.First().value = value;
context.SaveChanges();
return;
}
else if (ev.Count() == 0)
{
// sinon on l'ajoute
HedgingPortfolio p = new HedgingPortfolio { date = date, value =value };
context.Portfolio.Add(p);
context.SaveChanges();
return;
}
else
{
throw new Exception("Problème dans la BD.");
}
}
}
public static void storeInDB(string json, qpcptfaw context, string source, string code)
{
JObject jObj = JObject.Parse(json);
JArray datas = (JArray)jObj["data"];
//List<Irate> listInterestRate_db;
//List<Currencies> list_currencies_db;
Irate current_interestrate = getInterestFromQuandlCode(code);
//Currencies current_currency = getCurrencyFromQuandlCode(code);
//Si la BD ne contient pas ce currency, on le crée
if (!Access.InterestRateContains(current_interestrate))//!list_currencies_db.Contains(current_currency))
{
RateDB rate = new RateDB { rate = current_interestrate, name = current_interestrate.ToString()};
//ForexDB fdb = new ForexDB { currency = current_currency };
context.InteresRatesType.Add(rate);
context.SaveChanges();
}
//FIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIlAAAAAAAAAAAAAAAAAAAA YAAAAAAAAAAAAAAAAM
List<RateDBValue> list_rates = new List<RateDBValue>();
double value = 0;
//récupère l'id de la devise
int id = Access.getInterestRateIdFromIrate(current_interestrate);
if (id != -1)
{
foreach (var item in datas.Children())
{
JToken[] data = item.ToArray();
DateTime date = DateTime.Parse(data[0].ToString());
// keyValue = new KeyValuePair<int, DateTime>(id, date);
//pourquoi il yavait ça pour le currency
if (!Access.InterestRatesContainsKey(context, date, id)) //if (!list_pair_db.Contains(keyValue))
{
value = double.Parse(data[1].ToString());
RateDBValue r = new RateDBValue { date = date, RateDBId = id, value = value};
list_rates.Add(r);
}
}
}
list_rates.ForEach(p => context.Rates.Add(p));
context.SaveChanges();
}
public static void ClearDbConnections(qpcptfaw context)
{
var conns = from a in context.DbConnections
select a;
foreach (var a in conns)
{
context.DbConnections.Remove(a);
}
context.SaveChanges();
}
public static void ClearPrice(qpcptfaw context, int id)
{
var assets = from a in context.Prices
where a.AssetDBId==id
select a;
foreach (var a in assets)
{
context.Prices.Remove(a);
}
context.SaveChanges();
}
public static void ClearPrices(qpcptfaw context)
{
var assets = from a in context.Prices
select a;
foreach (var a in assets)
{
context.Prices.Remove(a);
}
context.SaveChanges();
}
public static void Clear_Prices_After(DateTime date, int id)
{
using (var context = new qpcptfaw())
{
var prices = from f in context.Prices
where f.date > date && f.AssetDBId == id
select f;
foreach (Price p in prices) context.Prices.Remove(p);
context.SaveChanges();
}
}
public static void Clear_Everglades_Price(DateTime date){
using(var context = new qpcptfaw()){
int id = GetIdEverglades();
var everg_price = from f in context.Prices
where f.AssetDBId == id && f.date == date
select f;
if (everg_price.Count() == 0) throw new ArgumentException("no everglades prices for this date", date.ToString());
if (everg_price.Count() > 1) throw new ArgumentException("there shoud be an unique price for this date", date.ToString());
context.Prices.Remove(everg_price.First());
context.SaveChanges();
}
}
public static Dictionary<int, double> getHedgingPortfolioTotalComposition(DateTime date)
{
Dictionary<int, double> composition = new Dictionary<int, double>();
DateTime dateLoop = date;
using (var context = new qpcptfaw())
{
System.Linq.IQueryable<AccessBD.PortfolioComposition> comp = null;
int i;
for (i = 0; i < 20; i++)
{
comp = from c in context.PortCompositions
where c.date == dateLoop
select c;
if (comp.Count() > 0)
{
break;
}
dateLoop = dateLoop - TimeSpan.FromDays(1);
}
if (comp == null || comp.Count() == 0) throw new ArgumentException("No data for this date", date.ToString());
// if data from past date, save at the right date
if (i > 1)
{
foreach (var a in comp)
{
AccessBD.PortfolioComposition b = new AccessBD.PortfolioComposition
{ AssetDB = a.AssetDB, AssetDBId = a.AssetDBId, date = date, quantity = a.quantity };
context.PortCompositions.Add(b);
}
context.SaveChanges();
}
// get composition in the dictionnary
foreach (var b in comp)
{
composition[b.AssetDBId] = b.quantity;
}
return composition;
}
}
public static double getPortfolioComposition(int AssetId, DateTime date)
{
using (var context = new qpcptfaw())
{
System.Linq.IQueryable<AccessBD.PortfolioComposition> comp = null;
int i;
for (i = 0; i < 20; i++)
{
comp = from c in context.PortCompositions
where c.AssetDBId == AssetId && c.date == date
select c;
if (comp.Count() > 0)
{
break;
}
date = date - TimeSpan.FromDays(1);
}
if (i > 1)
{
foreach (var a in comp)
{
a.date = date;
context.PortCompositions.Add(a);
}
context.SaveChanges();
}
if (comp == null || comp.Count() == 0) throw new ArgumentException("No data for this date", date.ToString());
if (comp.Count() > 1) throw new Exception("Data should be unique.");
return comp.First().quantity;
// if data from past date, save at the right date
}
}
public static void storeInDB(string json, qpcptfaw context, string source, string code)
{
JObject jObj = JObject.Parse(json);
JArray datas = (JArray)jObj["data"];
//List<Currencies> list_currencies_db;
//tous les currencies présents dans la BD
/*try
{
list_currencies_db = Access.getAllCurrencies();
}catch (Exception){
list_currencies_db = new List<Currencies>();
}*/
Currencies current_currency = getCurrencyFromQuandlCode(code);
//Si la BD ne contient pas ce currency, on le crée
if (!Access.CurrenciesContains(current_currency))//!list_currencies_db.Contains(current_currency))
{
ForexDB fdb = new ForexDB{forex = current_currency, name = code, RateDBId = Access.getInterestRateIdFromIrate(getIrateFromCurrency(current_currency))};
context.Assets.Add(fdb);
context.SaveChanges();
}
//List<KeyValuePair<int, DateTime>> list_pair_db = Access.getAllForexRateKey(context);
//KeyValuePair<int, DateTime> keyValue;
List<Price> list_rates = new List<Price>();
double rate = 0;
//récupère l'id de la devise
int id = Access.getForexIdFromCurrency(current_currency);
if (id != -1)
{
foreach (var item in datas.Children())
{
JToken[] data = item.ToArray();
DateTime date = DateTime.Parse(data[0].ToString());
// keyValue = new KeyValuePair<int, DateTime>(id, date);
if (!Access.ForexRateContainsKey(context, date, id)) //if (!list_pair_db.Contains(keyValue))
{
rate = double.Parse(data[1].ToString());
Price f = new Price {price = rate, priceEur = 1/rate, date = date, AssetDBId = id};
list_rates.Add(f);
}
}
}
list_rates.ForEach(p => context.Prices.Add(p));
context.SaveChanges();
}
public static void storeInDB(string json, qpcptfaw context, string source, string code)
{
JObject jObj = JObject.Parse(json);
JArray datas = (JArray)jObj["data"];
//tous les symboles présents dans la BD
//List<string> list_symbols_db = Access.getAllEquitiesSymbol(context);
//récupère le symbole de l'action récupérée dans ce json
//string symbol = (string)jObj["code"];
string symbol = code;
int aid;
Currencies curr;
//récupère tous les clés id-date de la BD (table Prices)
//List<KeyValuePair<int, DateTime>> list_pair_db = Access.getAllPricesKey(context);
//KeyValuePair<int, DateTime> keyValue;
List<Price> list_prices = new List<Price>();
double c = 0;
double open = 0;
double high = 0;
double low = 0;
double volume = 0;
string name;
//si la bd ne contient pas le symbole concerné, on crée une nouvelle action et on la stocke dans la BD (table asset)
if (!Access.ContainsEquitySymbol(context, symbol))//!list_symbols_db.Contains(symbol))
{
name = (string)jObj["name"];
EquityDB e = new EquityDB { name = name, symbol = symbol, PriceCurrency = CurrencyAsset.getCurrencyOf(name) };
context.Assets.Add(e);
context.SaveChanges();
AssetDB.assetCounter();
}
//Yahoo ["Date","Open","High","Low","Close","Volume","Adjusted Close"]
//Google ["Date","Open","High","Low","Close","Volume"
//récupère l'id correspondant au symbole de l'action
aid = Access.GetEquityIdFromSymbol(symbol);
curr = Access.GetEquityCurrencyFromSymbol(symbol);
//si l'id est -1 alors le symbol n'existe pas dans la bd, on ne stocke pas le prix
if(aid!=-1){
//on parse les données json
foreach (var item in datas.Children())
{
//chaque item correspond aux données d'un jour
JToken[] data = item.ToArray();
DateTime date = DateTime.Parse(data[0].ToString());
//keyValue = new KeyValuePair<int, DateTime>(aid, date);
//si la bd ne contient pas les données pour cette action pour ce jour, on ajoute les données
if (!Access.ContainsPricesKey(context, aid, date)){//!list_pair_db.Contains(keyValue)){
open = double.Parse(data[1].ToString());
high = double.Parse(data[2].ToString());
low = double.Parse(data[3].ToString());
volume = double.Parse(data[5].ToString());
if (source == "YAHOO")
{
c = double.Parse(data[6].ToString());
}
else if (source == "GOOG")
{
c = double.Parse(data[4].ToString());
}
Price p = new Price { AssetDBId = aid, price = c, date = date, high = high, low=low, open=open, volume=volume };
//conversion en euro
Dictionary<string, double> dic = new Dictionary<string, double>();
dic.Add("open", open);
dic.Add("high", high);
dic.Add("low", low);
dic.Add("price", c);
Dictionary<string, double> pricesEur = new Dictionary<string, double>();
if (!curr.Equals(Currencies.EUR))
{
pricesEur = CurrencyAsset.convertToEuro(dic, curr, date, context);
p.priceEur = pricesEur["price"];
p.highEur = pricesEur["high"];
p.lowEur = pricesEur["low"];
p.openEur = pricesEur["open"];
//p.priceEur = CurrencyAsset.convertToEuro(p.price, curr, date, context);
}
else
{
p.priceEur = p.price;
p.highEur = p.high;
p.lowEur = p.low;
p.openEur = p.open;
}
list_prices.Add(p);
}
}
list_prices.ForEach(p => context.Prices.Add(p));
context.SaveChanges();
}
}
public static void storePortfolioComposition(DateTime date, int assetId, double quantity)
{
using (var context = new qpcptfaw())
{
var ev = from e in context.PortCompositions
where e.date == date && e.AssetDBId == assetId
select e;
// si la date existe déjà dans la table des prix on la remplace
if (ev.Count() == 1) //list_pair_db.Contains(new KeyValuePair<int, DateTime>(id, date)))
{
// on vérifie que la valeur de prix est différente
// si identiques on return (rien à faire)
if (ev.First().quantity == quantity)
{
return;
}
// sinon on remplace
ev.First().quantity = quantity;
context.SaveChanges();
return;
}
else if (ev.Count() == 0)
{
// sinon on l'ajoute
PortfolioComposition p = new PortfolioComposition { AssetDBId = assetId, date = date, quantity = quantity };
context.PortCompositions.Add(p);
context.SaveChanges();
return;
}else
{
throw new Exception("Problème dans la BD.");
}
}
}
public static void storeCholeskyMat(DateTime date, double[][] mat)
{
using (var context = new qpcptfaw())
{
// si la date existe déjà dans la table des prix on la remplace
if (Access.ContainsCorrelKey(context, date))//list_pair_db.Contains(new KeyValuePair<int, DateTime>(id, date)))
{
var mats = from m in context.Covariance
where m.date == date
select m;
foreach(var m in mats)
{
m.value = mat[m.indexX][m.indexY];
}
}
else
{
List<CovDB> list_cov = new List<CovDB>();
for (int i = 0; i < mat.Length; i++)
{
for (int j = i; j < mat.Length; j++)
{
list_cov.Add(new CovDB() { date = date, indexX = i, indexY = j, value = mat[i][j] });
}
}
foreach (CovDB c in list_cov) context.Covariance.Add(c);
}
context.SaveChanges();
return;
}
}
public static void ClearDbConnections(qpcptfaw context, int id)
{
var conns = from a in context.DbConnections
where a.LastConnectionDBId == id
select a;
foreach (var a in conns)
{
context.DbConnections.Remove(a);
}
context.SaveChanges();
}
public static CashDB getCashDB(DateTime date)
{
using (var context = new qpcptfaw())
{
int i;
System.Linq.IQueryable<AccessBD.CashDB> cash = null;
DateTime dateLoop = date;
for (i = 0; i < 20; i++)
{
cash = from p in context.Cash
where p.date == dateLoop
select p;
if (cash.Count() > 0)
{
break;
}
dateLoop = dateLoop - TimeSpan.FromDays(1);
}
if (cash == null || cash.Count() == 0) throw new ArgumentException("No data for this date", date.ToString());
CashDB cash0 = new CashDB { date = date, value = cash.First().value };
// if data from past date, save at the right date
if (i > 1)
{
context.Cash.Add(cash0);
context.SaveChanges();
}
return cash0;
}
}
public static void Clear_Portfolio_Composition(DateTime date, int assetId)
{
using (var context = new qpcptfaw())
{
var comp = from c in context.PortCompositions
where c.AssetDBId == assetId && c.date == date
select c;
if (comp.Count() == 0) throw new ArgumentException("no data for this date", date.ToString());
if (comp.Count() > 1) throw new ArgumentException("data should be unique for this date.", date.ToString());
context.PortCompositions.Remove(comp.First());
context.SaveChanges();
}
}
public static void Clear_Portfolio_Price(DateTime date)
{
using (var context = new qpcptfaw())
{
var portfolio = from p in context.Portfolio
where p.date == date
select p;
if (portfolio.Count() == 0) throw new ArgumentException("no portfolio value for this date", date.ToString());
if (portfolio.Count() > 1) throw new ArgumentException("there shoud be an unique price for this date", date.ToString());
context.Portfolio.Remove(portfolio.First());
context.SaveChanges();
}
}
public static void ClearHedgingPortValue(DateTime date)
{
using (var context = new qpcptfaw())
{
var assets = from a in context.Portfolio
where a.date == date
select a;
foreach (var a in assets)
{
a.value = 0;
}
context.SaveChanges();
}
}
public static void Clear_Everglades_Prices()
{
using (var context = new qpcptfaw())
{
int id = GetIdEverglades();
var everg_price = from f in context.Prices
where f.AssetDBId == id
select f;
foreach(var e in everg_price) context.Prices.Remove(e);
context.SaveChanges();
}
}
public static void ClearPortComposition(DateTime date)
{
using (var context = new qpcptfaw())
{
var assets = from a in context.PortCompositions
where a.date == date
select a;
foreach (var a in assets)
{
a.quantity = 0;
}
context.SaveChanges();
}
}
public static void DBInit(qpcptfaw context)
{
DateTime lastConn;
if (context.DbConnections.FirstOrDefault(p => p.date == context.DbConnections.Max(x => x.date)) == null)
{
lastConn = DBstart;
}
else
{
lastConn = Access.GetLastConnection(context);
}
List<Currencies> list_currencies = new List<Currencies> { Currencies.USD, Currencies.HKD, Currencies.GBP, Currencies.CHF };
List<string> list = new List<string> { "AAPL:US", "SAN:SM", "939:HK", "941:HK", "CSGN:VX", "XOM:US", "HSBA:LN", "1398:HK", "JNJ:US", "MSFT:US", "NESN:VX", "NOVN:VX", "PG", "ROG:VX", "SAN:FP", "SIE:GR", "TEF:SM", "FP:FP", "UBSG:VX", "VOD:LN" };
List<Irate> list_interest_rates = new List<Irate>{Irate.Euribor, Irate.Hibor, Irate.LiborCHF, Irate.LiborGBP, Irate.LiborUSD};
//récupération des données Quandl
if (DateTime.Compare(lastConn, DateTime.Today) <= 0)
{
DateTime begin = lastConn.AddDays(-1);
DateTime end = lastConn.AddYears(1);
while (DateTime.Compare(end, DateTime.Today) < 0)
{
//récupération des taux d'intérêts
QuandlInterestRate.storeAllInDB(list_interest_rates, context, begin, end);
//récupération des taux de change
QuandlDataExchange.storeAllInDB(list_currencies, context, begin, end);
//récupération des prix des actions
QuandlData.storeAllInDB(list, context, begin, end);
begin = end;
end = begin.AddYears(1);
LastConnectionDB conn = new LastConnectionDB { date = begin };
context.DbConnections.Add(conn);
context.SaveChanges();
}
//récupération des taux d'intérêts
QuandlInterestRate.storeAllInDB(list_interest_rates, context, begin, DateTime.Today);
//récupération des taux de change
QuandlDataExchange.storeAllInDB(list_currencies, context, begin, DateTime.Today);
QuandlData.storeAllInDB(list, context, begin, DateTime.Today);
LastConnectionDB connection = new LastConnectionDB { date = DateTime.Today };
context.DbConnections.Add(connection);
context.SaveChanges();
}
//ajout de Everglades
EvergladesData.addEverglades();
//Création d'un portefeuille s'il n'y en a pas
if (context.PortCompositions.Count() == 0)
{
List<int> list_eq = Access.Get_List_Equities_id();
List<int> list_forex = Access.Get_List_Forex_id();
foreach (int e in list_eq)
{
Write.storePortfolioComposition(DateTime.Today, e, 0);
}
foreach (int e in list_forex)
{
Write.storePortfolioComposition(DateTime.Today, e, 0);
}
Write.storePortfolioValue(DateTime.Today, 0);
}
}
public static void ClearAllMatrix()
{
using (var context = new qpcptfaw())
{
var mat = from m in context.Covariance
select m;
foreach (var m in mat)
{
context.Covariance.Remove(m);
context.SaveChanges();
}
}
}