public static double getInterestRate(int rateid, DateTime date)
{
using (var context = new qpcptfaw())
{
// we search for explicit date
RateDBValue[] rates = (from r in context.Rates
where r.date == date && r.RateDBId == rateid
select r).ToArray();
// if not found we serch for closest date
if (rates.Count() == 0) // descending
{
rates = (from r in context.Rates
where r.RateDBId == rateid && r.date < date
orderby r.date descending
select r).Take(1).ToArray();
}
if (rates.Count() == 0) // and ascending
{
rates = (from r in context.Rates
where r.RateDBId == rateid && r.date > date
orderby r.date ascending
select r).Take(1).ToArray();
}
// finally we return or throw exception if no date
if (rates.Count() == 0)
{
throw new ArgumentException("No Data in database for this rateid.");
}
else
{
return rates.First().value / 100.0;
}
}
}
public static List<int> Get_List_Equities_id()
{
using (var context = new qpcptfaw())
{
var assets = from b in context.Assets.OfType<EquityDB>()
select b.AssetDBId;
return assets.ToList();
}
}
public static List<int> Get_List_Forex_id()
{
using (var context = new qpcptfaw())
{
var assets = from b in context.Assets.OfType<ForexDB>()
where b.forex != Currencies.EUR
select b.AssetDBId;
return assets.ToList();
}
}
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 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 double get_irate_from_currency(Currencies c, DateTime date) {
using (var context = new qpcptfaw())
{
int irateid;
if (c == Currencies.EUR)
{
irateid = 1;
}
else
{
var currencies = from curr in context.Assets.OfType<ForexDB>()
where curr.forex == c
select curr;
irateid = currencies.First().RateDBId;
}
return getInterestRate(irateid, date);
}
}
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 double convertToEuro(double price, Currencies currency, DateTime date, qpcptfaw context){
if (currency.Equals(Currencies.EUR)) return price;
double rate = 1;
bool isException = true;
DateTime datelocal = date;
while (isException)
{
try
{
rate = Access.getExchangeRate(currency, datelocal, context);
isException = false;
}
catch (Exception)
{
datelocal = datelocal.AddDays(-1);
}
}
return price/rate;
}
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 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_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 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 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();
}
}
/// <summary>
/// /////////////////////////////////////////////////////////////////////////////
/// </summary>
/// <param name="currency"></param>
/// <param name="context"></param>
/// <returns></returns>
public static double getFirstExchangeRate(Currencies currency, qpcptfaw context)
{
int cid;
if (_id_forex.ContainsKey(currency))
{
cid = _id_forex[currency];
}
else
{
cid = getForexIdFromCurrency(currency);
_id_forex.Add(currency, cid);
}
var rates = from r in context.Prices
where r.AssetDBId == cid
select r;
return rates.OrderBy(x => x.date).First().price;
}
public static Dictionary<int, double> getHedgingPortfolio(DateTime date)
{
Dictionary<int, double> composition = new Dictionary<int, double>();
using (var context = new qpcptfaw())
{
System.Linq.IQueryable<AccessBD.PortfolioComposition> comp = null;
for (int i = 0; i < 20; i++)
{
comp = from c in context.PortCompositions
where c.date == date
select c;
if (comp.Count() > 0)
{
break;
}
}
if (comp == null || comp.Count() == 0) throw new ArgumentException("No data for this date", date.ToString());
foreach (var a in comp)
{
composition[a.AssetDBId] = a.quantity;
}
return composition;
}
}
public static bool ContainsCashKey(qpcptfaw context, DateTime date)
{
var prices = from p in context.Cash
where p.date == date
select p;
if (prices.Count() == 0) return false;
if (prices.Count() == 1) return true;
throw new Exception("Data should be unique.");
}
public static bool InterestRatesContainsKey(qpcptfaw context, DateTime date, int id)
{
var rates = from r in context.Rates
where r.date == date && r.RateDBId == id
select r;
if (rates.Count() == 0) return false;
if (rates.Count() == 1) return true;
throw new Exception("The data returned should be unique. There is a problem in the Database.");
}
public static Dictionary<string, double> convertToEuro(Dictionary<string, double> prices, Currencies currency, DateTime date, qpcptfaw context)
{
if (currency.Equals(Currencies.EUR)) return prices;
Dictionary<string, double> p_converti = new Dictionary<string, double>();
double rate = 1;
bool isException = true;
DateTime datelocal = date;
while (isException && datelocal>=DBInitialisation.DBstart)
{
try
{
rate = Access.getExchangeRate(currency, datelocal, context);
isException = false;
}
catch (Exception)
{
datelocal = datelocal.AddDays(-1);
}
}
if (datelocal < DBInitialisation.DBstart)
{
rate = Access.getFirstExchangeRate(currency, context);
}
foreach (KeyValuePair<string,double> p in prices)
{
p_converti.Add(p.Key, p.Value / rate);
}
return p_converti;
}
public static double getHedgingPortfolioValue(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());
return portfolio.First().value;
}
}
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 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 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 List<string> Get_List_Assets()
{
List<string> list_assets = new List<string>();
using (var context = new qpcptfaw())
{
var assets = from b in context.Assets.OfType<EquityDB>()
select b;
foreach (var asset in assets)
{
list_assets.Add(asset.name);
}
}
return list_assets;
}
/*public static double[][] getCholeskyMatrix(DateTime date)
{
using (var context = new qpcptfaw())
{
double[][] mat;
DateTime datePlus = date - TimeSpan.FromDays(15);
var mats = (from m in context.CorrelVol
where m.date <= date && m.date >= datePlus
select m).ToArray();
if (mats.Length == 0)
{
throw new IndexOutOfRangeException("No data for this date");
}
else
{
CorrelDB matDB = mats.OrderBy(m => m.date).Last();
int l = matDB.matrix.Length;
mat = new double[l][];
for(int i = 0; i<l; i++) {
mat[i] = new double[l];
for(int j = 0; j<l; j++) {
mat[i][j] = matDB.matrix[i][j];
}
}
}
return mat;
}
}*/
/*public static double[] getVolatilityVector(DateTime date)
{
using (var context = new qpcptfaw())
{
var mats = from m in context.CorrelVol
where m.date <= date && m.date >= date.AddDays(-15)
select m;
return mats.OrderBy(m => m.date).Last().vol;
}
}*/
public static bool ContainsPortCompositionsKey(qpcptfaw context, int id, DateTime date)
{
var prices = from p in context.PortCompositions
where p.AssetDBId == id && p.date == date
select p;
if (prices.Count() == 0) return false;
if (prices.Count() == 1) return true;
throw new Exception("Data should be unique.");
}
public static double getExchangeRate(Currencies currency, DateTime date, qpcptfaw context)
{
int cid;
if (_id_forex.ContainsKey(currency))
{
cid = _id_forex[currency];
}
else
{
cid = getForexIdFromCurrency(currency);
_id_forex.Add(currency, cid);
}
var rates = from r in context.Prices
where r.AssetDBId == cid && r.date == date
select r;
if (rates.Count() == 0) throw new ArgumentException("No data for this date and currency", date.ToString());
if (rates.Count() > 1) throw new Exception("The data required should be unique.");
return rates.First().price;
}
/*public static List<Currencies> getAllCurrencies()
{
using (var context = new qpcptfaw())
{
List<Currencies> list_res = new List<Currencies>();
var currencies = from f in context.Forex
select f;
if (currencies.Count() == 0) throw new Exception("No currencies stored in the database");
foreach (var c in currencies) list_res.Add(c.currency);
return list_res;
}
}*/
public static bool CurrenciesContains(Currencies c)
{
using (var context = new qpcptfaw())
{
var currencies = from f in context.Assets.OfType<ForexDB>()
where f.forex == c
select f;
if (currencies.Count() == 1) return true;
if (currencies.Count() == 0) return false;
throw new Exception("The data should be unique. Problem in the database.");
}
}
public static List<DateTime> getAllKeysHedgingPortfolio(qpcptfaw context)
{
List<DateTime> list_dates = new List<DateTime>();
var portfolio = from p in context.Portfolio
select p;
foreach (var p in portfolio) list_dates.Add(p.date);
return list_dates;
}
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 int getForexIdFromCurrency(Currencies c)
{
int id = -1;
using (var context = new qpcptfaw())
{
var a = from currency in context.Assets.OfType<ForexDB>()
where currency.forex == c
select currency;
if (a.Count() == 0) throw new ArgumentException("symbol does not exist in the database", c.ToString());
if (a.Count() > 1) throw new ArgumentException("duplicate symbol in the database", c.ToString());
id = a.First().AssetDBId;
return id;
}
}
