public void FeedExcelDataForBonds(OleDbDataReader oleReader)
{
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext()) {
while (oleReader.Read())
{
string bondTypeName = oleReader.GetString(3);
Edis.Db.BondType bondType = db.BondTypes.FirstOrDefault(t => t.TypeName == bondTypeName);
if (bondType == null)
{
bondType = new Edis.Db.BondType {
Id = Guid.NewGuid().ToString(),
TypeName = bondTypeName
};
db.BondTypes.Add(bondType);
}
db.Bonds.Add(new Edis.Db.Assets.Bond {
BondId = Guid.NewGuid().ToString(),
Ticker = oleReader.GetString(0),
Name = oleReader.GetString(1),
Frequency = GetValueFromDescription <Frequency>(oleReader.GetString(2)),
BondType = bondType.Id,
Issuer = oleReader.GetString(4)
});
}
db.SaveChanges();
}
}
static void ImportDataFromExcelStockPrices()
{
Console.WriteLine("Starting...");
Excel.Application MyApp = new Excel.Application();
Excel.Workbook MyBook = MyApp.Workbooks.Open("C:/Users/ahksysuser06/Desktop/EDISData/Australian Equity Daily Closing Price 20131211.xlsx");
Excel.Worksheet MySheet = MyBook.Sheets[1];
DateTime startDate = new DateTime(2003, 01, 01);
DateTime endDate = new DateTime(2013, 12, 11);
// lost 182,1247, 1922, 2015, 2043 processing 2044 ARR AustralianEquity
for (int i = 2044; i <= MySheet.Rows.Count; i++) //MySheet.Rows.Count
{
Console.WriteLine("Processing row " + i.ToString());
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext())
{
//List<AssetPrice> assetPrices = new List<AssetPrice>();
int j = 8;
// Edis.Db.Assets.Equity equity = edisRepo.getEquityByTicker(MySheet.Cells[i, 4].Value.ToString(), EquityTypes.AustralianEquity);
string ticker = MySheet.Cells[i, 4].Value.ToString();
var equity = db.Equities.FirstOrDefault(e => e.Ticker == ticker && e.EquityType == EquityTypes.AustralianEquity);
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
{
var price = new AssetPrice
{
AssetType = AssetTypes.AustralianEquity,
CorrespondingAssetKey = equity.AssetId,
CreatedOn = date,
Price = MySheet.Cells[i, j].Value == null ? 0 : Convert.ToDouble(MySheet.Cells[i, j].Value.ToString()),
};
equity.Prices.Add(new AssetPrice
{
Id = Guid.NewGuid().ToString(),
AssetType = price.AssetType,
CreatedOn = price.CreatedOn,
CorrespondingAssetKey = price.CorrespondingAssetKey,
Price = price.Price
});
j++;
}
db.SaveChanges();
//edisRepo.InsertStockPricesData(assetPrices);
}
}
}
public void FeedExcelDataForEquities(OleDbDataReader oleReader)
{
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext()) {
while (oleReader.Read())
{
db.Equities.Add(new Edis.Db.Assets.Equity {
AssetId = Guid.NewGuid().ToString(),
Ticker = oleReader.GetString(0),
Name = oleReader.GetString(1),
Sector = oleReader.GetString(2),
EquityType = GetValueFromDescription <EquityTypes>(oleReader.GetString(3))
});
}
db.SaveChanges();
}
}
static void ImportDataFromExcelStockPrices()
{
Console.WriteLine("Starting...");
Excel.Application MyApp = new Excel.Application();
Excel.Workbook MyBook = MyApp.Workbooks.Open("C:/Users/ahksysuser06/Desktop/EDISData/Australian Equity Daily Closing Price 20131211.xlsx");
Excel.Worksheet MySheet = MyBook.Sheets[1];
DateTime startDate = new DateTime(2003, 01, 01);
DateTime endDate = new DateTime(2013, 12, 11);
// lost 182,1247, 1922, 2015, 2043 processing 2044 ARR AustralianEquity
for (int i = 2044; i <= MySheet.Rows.Count; i++) //MySheet.Rows.Count
{
Console.WriteLine("Processing row " + i.ToString());
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext())
{
//List<AssetPrice> assetPrices = new List<AssetPrice>();
int j = 8;
// Edis.Db.Assets.Equity equity = edisRepo.getEquityByTicker(MySheet.Cells[i, 4].Value.ToString(), EquityTypes.AustralianEquity);
string ticker = MySheet.Cells[i, 4].Value.ToString();
var equity = db.Equities.FirstOrDefault(e => e.Ticker == ticker && e.EquityType == EquityTypes.AustralianEquity);
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
{
var price = new AssetPrice
{
AssetType = AssetTypes.AustralianEquity,
CorrespondingAssetKey = equity.AssetId,
CreatedOn = date,
Price = MySheet.Cells[i, j].Value == null ? 0 : Convert.ToDouble(MySheet.Cells[i, j].Value.ToString()),
};
equity.Prices.Add(new AssetPrice
{
Id = Guid.NewGuid().ToString(),
AssetType = price.AssetType,
CreatedOn = price.CreatedOn,
CorrespondingAssetKey = price.CorrespondingAssetKey,
Price = price.Price
});
j++;
}
db.SaveChanges();
//edisRepo.InsertStockPricesData(assetPrices);
}
}
}
public void FeedExcelDataForResearchValue(OleDbDataReader oleReader)
{
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext()) {
while (oleReader.Read())
{
var value = oleReader[2].ToString();
string ticker = oleReader.GetString(0);
var equity = db.Equities.FirstOrDefault(e => e.Ticker == ticker);
var bond = db.Bonds.FirstOrDefault(e => e.Ticker == ticker);
if (equity != null)
{
equity.ResearchValues.Add(new Edis.Db.ResearchValue {
Id = Guid.NewGuid().ToString(),
Key = oleReader.GetString(1),
Value = value == "" ? 0 : (double?)double.Parse(value),
StringValue = oleReader[3].ToString(),
Issuer = oleReader.GetString(4),
CreatedOn = oleReader.GetDateTime(5),
});
}
else if (bond != null)
{
bond.ResearchValues.Add(new Edis.Db.ResearchValue {
Id = Guid.NewGuid().ToString(),
Key = oleReader.GetString(1),
Value = value == "" ? 0 : (double?)double.Parse(value),
StringValue = oleReader[3].ToString(),
Issuer = oleReader.GetString(4),
//CreatedOn = DateTime.Parse(createOn.ToString(), new CultureInfo("en-AU")).Date,
CreatedOn = oleReader.GetDateTime(5),
});
}
}
db.SaveChanges();
}
}
public void FeedExcelDataForAssetPrice(OleDbDataReader oleReader)
{
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext()) {
while (oleReader.Read())
{
AssetTypes assetType = GetValueFromDescription <AssetTypes>(oleReader.GetString(3));
var price = oleReader[1].ToString();
string ticker = oleReader.GetString(0);
var equity = db.Equities.FirstOrDefault(e => e.Ticker == ticker);
var bond = db.Bonds.FirstOrDefault(e => e.Ticker == ticker);
if (equity != null)
{
equity.Prices.Add(new Edis.Db.Assets.AssetPrice {
Id = Guid.NewGuid().ToString(),
Price = price == "" ? null : (double?)double.Parse(price),
CorrespondingAssetKey = equity.AssetId,
CreatedOn = oleReader.GetDateTime(2),
AssetType = assetType
});
}
else if (bond != null)
{
bond.Prices.Add(new Edis.Db.Assets.AssetPrice {
Id = Guid.NewGuid().ToString(),
Price = price == "" ? null : (double?)double.Parse(price),
CorrespondingAssetKey = bond.BondId,
CreatedOn = oleReader.GetDateTime(2),
AssetType = assetType
});
}
}
db.SaveChanges();
}
}
public void ImportDataFromExcelStockPrices()
{
Excel.Application MyApp = new Excel.Application();
Excel.Workbook MyBook = MyApp.Workbooks.Open("C:/Users/ahksysuser06/Desktop/EDISData/Australian Equity Daily Closing Price 20131211.xlsx");
Excel.Worksheet MySheet = MyBook.Sheets[1];
DateTime startDate = new DateTime(2003, 01, 01);
DateTime endDate = new DateTime(2013, 12, 11);
//120 ARR AustralianEquity
for (int i = 120; i <= MySheet.Rows.Count; i++) //MySheet.Rows.Count
{
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext())
{
//List<AssetPrice> assetPrices = new List<AssetPrice>();
int j = 8;
// Edis.Db.Assets.Equity equity = edisRepo.getEquityByTicker(MySheet.Cells[i, 4].Value.ToString(), EquityTypes.AustralianEquity);
string ticker = MySheet.Cells[i, 4].Value.ToString();
var equity = db.Equities.SingleOrDefault(e => e.Ticker == ticker && e.EquityType == EquityTypes.AustralianEquity);
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
{
var price =new AssetPrice
{
AssetType = AssetTypes.AustralianEquity,
CorrespondingAssetKey = equity.AssetId,
CreatedOn = date,
Price = MySheet.Cells[i, j].Value == null ? -1 : Convert.ToDouble(MySheet.Cells[i, j++].Value.ToString()),
};
equity.Prices.Add(new AssetPrice
{
Id = Guid.NewGuid().ToString(),
AssetType = price.AssetType,
CreatedOn = price.CreatedOn,
CorrespondingAssetKey = price.CorrespondingAssetKey,
Price = price.Price
});
}
db.SaveChanges();
//edisRepo.InsertStockPricesData(assetPrices);
}
}
//string excelFilePath = "C:/Users/ahksysuser06/Desktop/EDISData/Australian Equity Daily Closing Price 20131211.xlsx";
//// make sure your sheet name is correct, here sheet name is sheet1, so you can change the sheet name if have different
//List<DateTime> allDates = new List<DateTime>();
//string allDateString = "";
////for (DateTime date = startDate; date <= endDate; date = date.AddDays(1)) {
//// allDateString += date.ToString("dd-MM-yyyy") + " ,";
////}
////allDateString = allDateString.Substring(0, allDateString.Length - 1);
////string excelQuery = "select Symbol, " + allDateString + " from [Sheet1$]";
//string excelQuery = "select * from [Sheet1$]";
//try
//{
// string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath +
// ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
// OleDbConnection oleConn = new OleDbConnection(excelConnectionString);
// oleConn.Open();
// OleDbDataReader oleReader = new OleDbCommand(excelQuery, oleConn).ExecuteReader();
// List<AssetPrice> assetPrices = new List<AssetPrice>();
// while (oleReader.Read())
// {
// Edis.Db.Assets.Equity equity = edisRepo.getEquityByTicker(oleReader.GetString(3));
// int i = 7;
// for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
// {
// assetPrices.Add(new AssetPrice
// {
// AssetType = AssetTypes.AustralianEquity,
// CorrespondingAssetKey = equity.AssetId,
// CreatedOn = date,
// Price = (oleReader[i] == null || oleReader[i].ToString() == "") ? null : (double?)oleReader[i],
// });
// System.Diagnostics.Debug.WriteLine(AssetTypes.AustralianEquity + ", " + equity.AssetId + ", " + date.ToString("dd-MM-yyyy") + ", " + oleReader[1] + ", " + (oleReader[i] == null || oleReader[i].ToString() == "" ? null : (double?)oleReader[i]));
// i++;
// }
// System.Diagnostics.Debug.WriteLine(AssetTypes.AustralianEquity + ", " + equity.AssetId + ", " + oleReader[1] + ", " + (oleReader[1000] == null || oleReader[1000].ToString() == "" ? null : (double?)oleReader[1000]));
// //edisRepo.InsertStockPricesData();
// }
// oleReader.Close();
// oleConn.Close();
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
//}
}
public void ImportDataFromExcelStockPrices()
{
Excel.Application MyApp = new Excel.Application();
Excel.Workbook MyBook = MyApp.Workbooks.Open("C:/Users/ahksysuser06/Desktop/EDISData/Australian Equity Daily Closing Price 20131211.xlsx");
Excel.Worksheet MySheet = MyBook.Sheets[1];
DateTime startDate = new DateTime(2003, 01, 01);
DateTime endDate = new DateTime(2013, 12, 11);
//120 ARR AustralianEquity
for (int i = 120; i <= MySheet.Rows.Count; i++) //MySheet.Rows.Count
{
using (Edis.Db.EdisContext db = new Edis.Db.EdisContext())
{
//List<AssetPrice> assetPrices = new List<AssetPrice>();
int j = 8;
// Edis.Db.Assets.Equity equity = edisRepo.getEquityByTicker(MySheet.Cells[i, 4].Value.ToString(), EquityTypes.AustralianEquity);
string ticker = MySheet.Cells[i, 4].Value.ToString();
var equity = db.Equities.SingleOrDefault(e => e.Ticker == ticker && e.EquityType == EquityTypes.AustralianEquity);
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
{
var price = new AssetPrice
{
AssetType = AssetTypes.AustralianEquity,
CorrespondingAssetKey = equity.AssetId,
CreatedOn = date,
Price = MySheet.Cells[i, j].Value == null ? -1 : Convert.ToDouble(MySheet.Cells[i, j++].Value.ToString()),
};
equity.Prices.Add(new AssetPrice
{
Id = Guid.NewGuid().ToString(),
AssetType = price.AssetType,
CreatedOn = price.CreatedOn,
CorrespondingAssetKey = price.CorrespondingAssetKey,
Price = price.Price
});
}
db.SaveChanges();
//edisRepo.InsertStockPricesData(assetPrices);
}
}
//string excelFilePath = "C:/Users/ahksysuser06/Desktop/EDISData/Australian Equity Daily Closing Price 20131211.xlsx";
//// make sure your sheet name is correct, here sheet name is sheet1, so you can change the sheet name if have different
//List<DateTime> allDates = new List<DateTime>();
//string allDateString = "";
////for (DateTime date = startDate; date <= endDate; date = date.AddDays(1)) {
//// allDateString += date.ToString("dd-MM-yyyy") + " ,";
////}
////allDateString = allDateString.Substring(0, allDateString.Length - 1);
////string excelQuery = "select Symbol, " + allDateString + " from [Sheet1$]";
//string excelQuery = "select * from [Sheet1$]";
//try
//{
// string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath +
// ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
// OleDbConnection oleConn = new OleDbConnection(excelConnectionString);
// oleConn.Open();
// OleDbDataReader oleReader = new OleDbCommand(excelQuery, oleConn).ExecuteReader();
// List<AssetPrice> assetPrices = new List<AssetPrice>();
// while (oleReader.Read())
// {
// Edis.Db.Assets.Equity equity = edisRepo.getEquityByTicker(oleReader.GetString(3));
// int i = 7;
// for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
// {
// assetPrices.Add(new AssetPrice
// {
// AssetType = AssetTypes.AustralianEquity,
// CorrespondingAssetKey = equity.AssetId,
// CreatedOn = date,
// Price = (oleReader[i] == null || oleReader[i].ToString() == "") ? null : (double?)oleReader[i],
// });
// System.Diagnostics.Debug.WriteLine(AssetTypes.AustralianEquity + ", " + equity.AssetId + ", " + date.ToString("dd-MM-yyyy") + ", " + oleReader[1] + ", " + (oleReader[i] == null || oleReader[i].ToString() == "" ? null : (double?)oleReader[i]));
// i++;
// }
// System.Diagnostics.Debug.WriteLine(AssetTypes.AustralianEquity + ", " + equity.AssetId + ", " + oleReader[1] + ", " + (oleReader[1000] == null || oleReader[1000].ToString() == "" ? null : (double?)oleReader[1000]));
// //edisRepo.InsertStockPricesData();
// }
// oleReader.Close();
// oleConn.Close();
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
//}
}
