public static void CalculateCpiDifferenceQuaterly()
{
using (ForexModel context = new ForexModel())
{
var ukRates = context.CpiQuaterlyRates.Where(c => c.Country == "\"GBR\"").OrderBy(y => y.Date).ToList();
var usRates = context.CpiQuaterlyRates.Where(c => c.Country == "\"USA\"").OrderBy(y => y.Date).ToList();
for (int i = 0; i < ukRates.Count; i++)
{
CpiQuaterlyRate ukRate = ukRates[i];
CpiQuaterlyRate usRate = usRates[i];
var year = Int32.Parse(ukRate.Date.Substring(1, 4));
var quater = Int32.Parse(ukRate.Date.Substring(7, 1));
CpiQuaterlyDifference difference = new CpiQuaterlyDifference
{
Id = i,
Year = year,
Quarter = quater,
Difference = ukRate.Rate - usRate.Rate
};
context.CpiQuaterlyDifferences.Add(difference);
}
context.SaveChanges();
}
}
public static void UploadAnalyticQuarterlyRecords()
{
using (ForexModel context = new ForexModel())
{
var cpiQuarterly = context.CpiQuaterlyDifferences.ToList();
var interestRateQuarterly = context.InterestRateQuaterlyDifferences.ToList();
for (int i = 4; i < interestRateQuarterly.Count; i++)
{
int year = interestRateQuarterly[i].Year;
int quarter = interestRateQuarterly[i].Quarter;
int eligibleMonth = DetermineEligibleStartMonth(quarter);
AnalyticQuarterlyRecord record = new AnalyticQuarterlyRecord
{
Id = i,
Year = year,
Quarter = quarter,
CpiDifference = cpiQuarterly[i].Difference,
CpiTendency = CalculateTendencyForQuarterly(cpiQuarterly, year, quarter),
InterestRateDifference = interestRateQuarterly
.Where(c => c.Year == year && c.Quarter == quarter).Select(v => v.Difference).Single(),
InterestRateTendency = CalculateTendencyForQuarterly(interestRateQuarterly, year + 1, quarter),
TradeBalanceByUk = context.TradeBalances.Where(c => c.Country == "UK" && c.Year == year)
.Select(v => v.Balance).Single(),
TradeBalanceByUs = context.TradeBalances.Where(c => c.Country == "USA" && c.Year == year)
.Select(v => v.Balance).Single(),
DebtGrowthUk = context.DebtGrowths.Where(c => c.Country == "UK" && c.Year == year)
.Select(v => v.Growth)
.Single(),
DebtGrowthUs = context.DebtGrowths.Where(c => c.Country == "USA" && c.Year == year)
.Select(v => v.Growth)
.Single(),
ForexTendency = context.GbpUsdGrowthQuaterlies
.Where(y => y.Date.Year == year - 1 ||
(y.Date.Year == year && y.Date.Month <= eligibleMonth))
.Select(v => v.CloseGrowth).Average(),
Outcome = GetOutcomeLabel(context.GbpUsdGrowthQuaterlies
.Where(y => y.Date.Year == year && y.Date.Month == eligibleMonth)
.Select(v => v.CloseGrowth).Single())
};
context.AnalyticQuarterlyRecords.Add(record);
}
context.SaveChanges();
}
}
public static void UploadAnalyticDailyRecords()
{
using (ForexModel context = new ForexModel())
{
var forexGrowths = context.GbpUsdGrowth.ToList();
for (int i = 260; i < forexGrowths.Count; i++)
{
var date = forexGrowths[i].Date;
var recDate = date.AddMonths(-3);
AnalyticDailyRecord record = new AnalyticDailyRecord();
record.Id = i;
record.Date = date;
record.CpiDifference = context.CpiYearlyDifferences.Where(d => d.Year == date.Year)
.Select(v => v.Difference).Single();
record.CpiTendency = CalculateTendency(context.CpiQuaterlyDifferences.ToList(), date.Year);
record.InterestRateDifference = context.InterestRateYearlyDifferences.Where(c => c.Year == date.Year)
.Select(v => v.Difference).Single();
record.InterestRateTendency = CalculateTendency(context.InterestRateQuaterlyDifferences.ToList(), date.Year);
record.TradeBalanceByUk = context.TradeBalances.Where(c => c.Country == "UK" && c.Year == date.Year)
.Select(v => v.Balance).Single();
record.TradeBalanceByUs = context.TradeBalances.Where(c => c.Country == "USA" && c.Year == date.Year)
.Select(v => v.Balance).Single();
record.DebtGrowthUk = context.DebtGrowths.Where(c => c.Country == "UK" && c.Year == date.Year)
.Select(v => v.Growth)
.Single();
record.DebtGrowthUs = context.DebtGrowths.Where(c => c.Country == "USA" && c.Year == date.Year)
.Select(v => v.Growth)
.Single();
record.ForexTendency = context.GbpUsdGrowth.Where(y => y.Date <date && y.Date> recDate)
.Select(v => v.CloseGrowth).Average();
record.Outcome = GetOutcomeLabel(forexGrowths.Where(d => d.Date == date).Select(v => v.CloseGrowth)
.Single());
context.AnalyticDailyRecords.Add(record);
}
context.SaveChanges();
}
}
public static void CalculateTradeBalance(string country)
{
using (ForexModel context = new ForexModel())
{
var data = context.Trades.Where(c => c.Country == country);
var exports = data.Where(f => f.Flow == 2).OrderBy(y => y.Year).ToList();
var imports = data.Where(f => f.Flow == 1).OrderBy(y => y.Year).ToList();
for (int i = 0; i < exports.Count; i++)
{
TradeBalance balance = new TradeBalance
{
Country = country,
Year = exports[i].Year,
Balance = exports[i].Value - imports[i].Value
};
context.TradeBalances.Add(balance);
}
context.SaveChanges();
}
}
public static void CalculateDebtGrowth(string country)
{
using (ForexModel context = new ForexModel())
{
var data = context.Debts.Where(c => c.Country == country).ToList();
for (int i = 1; i < data.Count; i++)
{
double currentValue = data[i].PercentageDebt;
double previousValue = data[i - 1].PercentageDebt;
var item = new DebtGrowth
{
Country = country,
Year = data[i].Year,
Growth = CalculateGrowth(previousValue, currentValue)
};
context.DebtGrowths.Add(item);
}
context.SaveChanges();
}
}
public static void UploadAnalyticRecords()
{
using (ForexModel context = new ForexModel())
{
var cpiYearly = context.CpiYearlyDifferences.ToList();
for (int i = 1; i < cpiYearly.Count; i++)
{
int year = cpiYearly[i].Year;
AnalyticRecord record = new AnalyticRecord();
record.Id = i;
record.Year = year;
record.CpiDifference = cpiYearly[i].Difference;
record.CpiTendency = CalculateTendency(context.CpiQuaterlyDifferences.ToList(), year);
record.InterestRateDifference = context.InterestRateYearlyDifferences.Where(c => c.Year == year)
.Select(v => v.Difference).Single();
record.InterestRateTendency =
CalculateTendency(context.InterestRateQuaterlyDifferences.ToList(), year);
record.TradeBalanceByUk = context.TradeBalances.Where(c => c.Country == "UK" && c.Year == year)
.Select(v => v.Balance).Single();
record.TradeBalanceByUs = context.TradeBalances.Where(c => c.Country == "USA" && c.Year == year)
.Select(v => v.Balance).Single();
record.DebtGrowthUk = context.DebtGrowths.Where(c => c.Country == "UK" && c.Year == year)
.Select(v => v.Growth)
.Single();
record.DebtGrowthUs = context.DebtGrowths.Where(c => c.Country == "USA" && c.Year == year)
.Select(v => v.Growth)
.Single();
record.ForexTendency = context.GbpUsdGrowthQuaterlies.Where(y => y.Date.Year == year - 1)
.Select(v => v.CloseGrowth).Average();
record.Outcome = GetOutcomeLabel(context.GbpUsdGrowth.Where(y => y.Date.Year == year)
.Select(v => v.CloseGrowth).First());
context.AnalyticRecords.Add(record);
}
context.SaveChanges();
}
}
public static void CalculateInterestRateDifference(Period period)
{
using (ForexModel context = new ForexModel())
{
var ukDataRaw = context.UkInterestRates.ToList();
var ukData = ukDataRaw.Select(v => new InterestRate
{
Country = "UK",
Rate = v.Rate,
Date = ParseDate(v.Date)
}).OrderBy(d => d.Date)
.ToList();
var usData = context.UsMonthlyInterestRates.Select(v => new InterestRate
{
Country = "USA",
Rate = v.Rate,
Date = v.Date
}).ToList();
int ukMinYear = ukData.Select(y => y.Date.Year).Min();
int usMinYear = usData.Select(y => y.Date.Year).Min();
int minYear = ukMinYear >= usMinYear ? ukMinYear : usMinYear;
int ukMaxYear = ukData.Select(y => y.Date.Year).Max();
int usMaxYear = usData.Select(y => y.Date.Year).Max();
int maxYear = ukMaxYear <= usMaxYear ? ukMaxYear : usMaxYear;
for (int i = minYear; i <= maxYear; i++)
{
if (period == Period.Quaterly)
{
int month = 1;
for (int j = 1; j <= 4; j++)
{
if (!(i == 2017 && j > 1))
{
DateTime ukDate = DateHelpers.GetFirstAvailableDay(ukData, i, month);
DateTime usDate = DateHelpers.GetFirstAvailableDay(usData, i, month);
double ukRate = ukData.Where(d => d.Date == ukDate).Select(r => r.Rate).First();
double usRate = usData.Where(d => d.Date == usDate).Select(r => r.Rate).First();
InterestRateQuaterlyDifference difference = new InterestRateQuaterlyDifference
{
Year = i,
Quarter = j,
Difference = ukRate - usRate
};
context.InterestRateQuaterlyDifferences.Add(difference);
month += 3;
}
}
}
else if (period == Period.Yearly)
{
DateTime ukDate = DateHelpers.GetFirstAvailableDay(ukData, i, 1);
DateTime usDate = DateHelpers.GetFirstAvailableDay(usData, i, 1);
double ukRate = ukData.Where(d => d.Date == ukDate).Select(r => r.Rate).First();
double usRate = usData.Where(d => d.Date == usDate).Select(r => r.Rate).First();
InterestRateYearlyDifference difference = new InterestRateYearlyDifference
{
Year = i,
Difference = ukRate - usRate
};
context.InterestRateYearlyDifferences.Add(difference);
}
}
context.SaveChanges();
}
}
