コード例 #1
0
        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();
            }
        }
コード例 #2
0
ファイル: Program.cs プロジェクト: stevenzhenhaowang/EDIS
        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);
                }

            }

        }
コード例 #3
0
 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();
     }
 }
コード例 #4
0
        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);
                }
            }
        }
コード例 #5
0
        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();
            }
        }
コード例 #6
0
        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();
            }
        }
コード例 #7
0
        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);
            //}
        }
コード例 #8
0
        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);
            //}
        }