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);
}
/// <summary>
/// Gets the out standing shares.
/// </summary>
/// <param name="simId">The sim identifier.</param>
/// <returns></returns>
public async Task <OutstandingShares> GetOutStandingShares(string simId)
{
var existingRecord = _dBConnection.Get(x => x.SimId == simId).FirstOrDefault();
if (existingRecord != null &&
existingRecord.LastUpdateDate != null &&
((TimeSpan)(DateTime.Now - existingRecord.LastUpdateDate)).Days < validityOfRecord)
{
return(Mapper.Map <OutstandingSharesMd, OutstandingShares>(existingRecord));
}
var externalData = await _dos.ObtainAggregatedList(simId);
if (externalData == null)
{
_logger.LogError($"Could not download outstanding shares for SimId:{simId}");
return(null);
}
string id = "";
if (existingRecord != null)
{
id = existingRecord.Id;
}
existingRecord = Mapper.Map <OutstandingShares, OutstandingSharesMd>(externalData);
existingRecord.LastUpdateDate = DateTime.Now;
if (!id.IsNullOrWhiteSpace())
{
existingRecord.Id = id;
await _dBConnection.Update(id, existingRecord);
}
else
{
await _dBConnection.Create(existingRecord);
}
return(externalData);
}
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);
}
}