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);
}
}
}
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 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);
//}
}
