public async Task <List <CompanyDetail> > GetAllCompaniesAsync()
{
var tmpList = await _dlf.GetCompanyList();
allCompanies = new List <CompanyDetailMd>();
var dbCompanies = _dbconCompany.Get().ToList();
if (dbCompanies.Count() < 100 || dbCompanies.Where(x => x.IndustryTemplate.IsNullOrWhiteSpace()).Count() < 20)
{
var deleteStatus = await _dbconCompany.RemoveAll();
if (deleteStatus == false)
{
return(null);
}
}
dbCompanies = _dbconCompany.Get().ToList();
if (tmpList == null)
{
return(Mapper.Map <List <CompanyDetailMd>, List <CompanyDetail> >(dbCompanies));
}
foreach (var company in tmpList)
{
var dbCompany = dbCompanies.Where(x => x.SimId == company.SimId).FirstOrDefault();
if (dbCompany != null)
{
dbCompany.Name = company.Name;
dbCompany.Ticker = company.Ticker;
allCompanies.Add(dbCompany);
}
else
{
allCompanies.Add(new CompanyDetailMd(company));
}
}
var insertStatus = await _dbconCompany.UpdateMultiple(allCompanies);
if (insertStatus)
{
var listOfAllCompanies = Mapper.Map <List <CompanyDetailMd>, List <CompanyDetail> >(allCompanies);
return(listOfAllCompanies);
}
return(null);
}
private async Task <LastUpateCollectionMd> PopulateSymbolsToDbAsync(LastUpateCollectionMd lastUpdateDate)
{
var symbolsMd = _mapper.Map <List <SecuritySymbolMd> >(symbols);
var updateResult = false;
if (lastUpdateDate == null)
{
lastUpdateDate = new LastUpateCollectionMd
{
CollectionName = SecSymobls,
LastUpdate = DateTime.Now
};
lastUpdateDate = await _connectionHandlerLU.Create(lastUpdateDate);
if (lastUpdateDate.Id.IsNullOrWhiteSpace())
{
_logger.LogCritical("Could not insert record to collection LastUpdate");
}
}
else
{
lastUpdateDate.LastUpdate = DateTime.Now;
updateResult = await _connectionHandlerLU.Update(lastUpdateDate.Id, lastUpdateDate);
if (!updateResult)
{
_logger.LogCritical("Could not update record in LastUpdate");
}
}
updateResult = await _connectionHandlerSS.RemoveAll();
if (!updateResult)
{
_logger.LogCritical("Could delete all records in SecuritySymbol");
}
updateResult = await _connectionHandlerSS.Create(symbolsMd);
if (!updateResult)
{
_logger.LogCritical("Could insert records in SecuritySymbol");
}
return(lastUpdateDate);
}
public async Task UpdateAnalysis()
{
var ac = await _hcl.GetAllCompaniesFromDbAsync();
try
{
await _dfr.ParseKeyFinanceFromS3(ServiceExtensions.BucketName, ServiceExtensions.Region, "1. Key Ratios.xlsx");
}
catch (Exception ex)
{
_logger.LogError($"Error while reading excel file\n{ex.Message}");
return;
}
var result = await _dbpiScore.RemoveAll();
_logger.LogDebug($"Excel file contains {_dfr.dataCollection.Count}");
List <DataCollection> dcl = _dfr.dataCollection;
List <CompanyDetail> allCompanies = await _hcl.GetAllCompaniesFromDbAsync();
var counter = 0;
var newValues = new List <PiotroskiScoreMd>();
var updateTime = DateTime.Now;
foreach (var dc in dcl)
{
if (string.IsNullOrWhiteSpace(dc.CompanyName) || string.IsNullOrWhiteSpace(dc.Ticker))
{
_logger.LogDebug($"Skipping {dc.CompanyName} => {dc.Ticker} due to missing details");
continue;
}
var selected = (from comp in allCompanies
where (comp.Ticker == dc.Ticker && comp.Name == dc.CompanyName)
select comp).FirstOrDefault();
if (selected == null)
{
_logger.LogDebug("Referential integrity error");
_logger.LogDebug($"Did not find {dc.CompanyName} => {dc.Ticker} in Company Details");
continue;
}
var ProfitablityRatios = new Dictionary <string, decimal>
{
{ "Gross Margin", (decimal)dc.GrossMargin },
{ "Operating Margin", (decimal)dc.OperatingMargin },
{ "Net Profit Margin", (decimal)dc.NetMargin },
{ "Return on Equity", (decimal)dc.ReturnOnEquity },
{ "Return on Assets", (decimal)dc.ReturnOnAssets }
};
var newValue = new PiotroskiScore
{
SimId = selected.SimId,
FYear = DateTime.Now.Year,
Rating = dc.PiotroskiScoreCurrent,
EBITDA = (long)dc.EbitdaCurrent,
LastUpdate = DateTime.Now,
ProfitablityRatios = ProfitablityRatios,
Revenue = dc.Revenue,
Ticker = dc.Ticker
};
newValues.Add(Mapper.Map <PiotroskiScoreMd>(newValue));
newValue.ProfitablityRatios.Clear();
UpdateAnalysis(newValue, DateTime.Now.Year - 1, dc.PiotroskiScore1YrAgo, dc.Ebitda1YrAgo);
newValues.Add(Mapper.Map <PiotroskiScoreMd>(newValue));
UpdateAnalysis(newValue, DateTime.Now.Year - 2, dc.PiotroskiScore2YrAgo, dc.Ebitda2YrAgo);
newValues.Add(Mapper.Map <PiotroskiScoreMd>(newValue));
UpdateAnalysis(newValue, DateTime.Now.Year - 3, dc.PiotroskiScore3YrAgo, dc.Ebitda3YrAgo);
newValues.Add(Mapper.Map <PiotroskiScoreMd>(newValue));
selected.LastUpdate = updateTime;
await _hcl.UpdateCompanyDetailAsync(selected.SimId, dc.Sector, updateTime);
if (++counter % 500 == 0)
{
Console.WriteLine($"Updated {counter} firms {DateTime.Now}");
_logger.LogDebug($"Updated {counter} firms");
await _dbpiScore.Create(newValues);
newValues.Clear();
}
}
if (newValues.Any())
{
Console.WriteLine($"Updated {counter} firms");
_logger.LogDebug($"Updated {counter} firms");
await _dbpiScore.Create(newValues);
}
}
