private static void UpdateAnalysis(PiotroskiScore newValue, int year, int rating, float ebitda)
{
newValue.FYear = year;
newValue.Rating = rating;
newValue.EBITDA = (long)ebitda;
newValue.Revenue = null;
}
public async Task <string> GetCompanyFundamentals(string companyName)
{
_logger.LogTrace("Starting fundamental details");
var tickersToUse = await _resolveCompanyName.ResolveCompanyNameOrTicker(companyName);
if (tickersToUse.IsNullOrWhiteSpace())
{
return($"We could not resolve ticker/company Name {companyName}; possible reason Google registered name is Alphabet Inc.\n\n" +
$"Hint: try with symbol or ticker");
}
var fulfillmentText = new StringBuilder();
try
{
int counter = 0;
foreach (var ticker in tickersToUse.Split(','))
{
var ratingsMd = _connectionHandlerCF.Get().Where(x => x.Ticker.ToLower().Equals(ticker.ToLower()))
.OrderByDescending(x => x.FYear).FirstOrDefault();
PiotroskiScore computedRating = null;
if (ratingsMd != null && DateTime.Now.Year - ratingsMd.FYear <= 2)
{
computedRating = _mapper.Map <PiotroskiScore>(ratingsMd);
}
fulfillmentText.Append(await BuildCompanyProfile(ticker, computedRating));
if (++counter >= 2)
{
break;
}
}
if (counter >= 2)
{
fulfillmentText.Append($"Limiting result set as the search term {companyName} resolved to too many results.\n");
}
}
catch (Exception ex)
{
_logger.LogCritical($"Error while processing Get Company Fundamentals; \n{ex.Message}");
return("");
}
if (fulfillmentText.ToString().Contains("Piotroski"))
{
fulfillmentText.Append("\nNote: Piotroski F-Score is based on company fundamentals; " +
"a rating greater than 6 indicates strong fundamentals.");
}
return(fulfillmentText.ToString());
}
private string BuildCommonStockMessage(PiotroskiScore computedRating, string symbol)
{
var returnText = new StringBuilder();
if (computedRating == null)
{
returnText.Append($" We do not have any additional details about {symbol} at this time.\n");
return(returnText.ToString());
}
returnText.Append($"Additional details about {symbol} using its SEC filings.\n");
returnText.Append($" Revenues {((decimal)computedRating.Revenue).ToKMB()}.\n");
returnText.Append($" EBITDA {((decimal)(computedRating.EBITDA)).ToKMB()}. \n");
returnText.Append($" Gross Margin {computedRating.ProfitablityRatios["Gross Margin"]}%.\n");
returnText.Append($" Operating Margin {computedRating.ProfitablityRatios["Operating Margin"]}%.\n");
returnText.Append($" Net Profit Margin {computedRating.ProfitablityRatios["Net Profit Margin"]}%.\n");
returnText.Append($" Return on Equity {computedRating.ProfitablityRatios["Return on Equity"]}%.\n");
returnText.Append($" Return on Assets {computedRating.ProfitablityRatios["Return on Assets"]}%.\n");
if (computedRating.Rating != 0)
{
returnText.Append($" Piotroski F-Score ratings for {symbol} is {computedRating.Rating}. \n");
}
return(returnText.ToString());
}
private async Task <string> BuildCompanyProfile(string ticker, PiotroskiScore computedRating)
{
var symbol = Regex.Replace(ticker, ".{1}", "$0 ");
var companyOverview = await ObtainCompanyOverview(ticker);
var companyStats = await ObtainCompanyStats(ticker);
companyStats.ttmDividendRate = companyStats.ttmDividendRate == null ? 0 :
companyStats.ttmDividendRate;
companyStats.ttmEPS = companyStats.ttmEPS == null ? 0 :
companyStats.ttmEPS;
var returnText = new StringBuilder();
if (companyOverview == null || string.IsNullOrWhiteSpace(companyOverview.Symbol))
{
returnText.Append($" No basic information about {symbol} found at this time\n");
}
else
{
returnText.Append($"Basic information about {companyOverview.CompanyName} trading with symbol {symbol}.\n");
returnText.Append($" {(companyOverview.Description.IsNullOrWhiteSpace() ? "This information is not available now" : companyOverview.Description.TruncateAtWord(175))}\n");
if (companyOverview.Description.Length >= 175)
{
returnText.Append("\n\n..... more removed. ....\n\n");
}
returnText.Append($" Its industry sector is {companyOverview.Sector} and falls under {companyOverview.Industry}.\n\n ");
returnText.Append($" {companyOverview.CompanyName} has a market cap of {((decimal)companyStats.marketcap).ToKMB()}, " +
$" TTM E P S of about {((decimal)companyStats.ttmEPS).ToKMB()}, and divident rate around {((decimal)companyStats.ttmDividendRate).ToKMB()}.\n");
}
switch (companyOverview.IssueType.ToLower())
{
case "cs":
returnText.Append(BuildCommonStockMessage(computedRating, symbol));
//1returnText.Append(await BuildAnalystsRatings(ticker));
break;
case "ad":
returnText.Append($" {symbol} is an American Depositary Receipt: ADR");
break;
case "re":
returnText.Append($" {symbol} is a Real estate investment trust: REIT");
break;
case "ce":
case "cef":
returnText.Append($" {symbol} is a closed end fund");
break;
case "si":
returnText.Append($" {symbol} is a secondary issue");
break;
case "et":
case "etf":
returnText.Append($" {symbol} is a exchange traded fund; ETF");
break;
case "ps":
case "wt":
returnText.Append($" {symbol} is a preferred stock or warrant; individuals do not trade these!");
break;
default:
returnText.Append($" No additional information is available for {symbol}");
break;
}
return(returnText.ToString());
}
public async Task <WebhookResponse> GetCompanyRatings(GoogleCloudDialogflowV2WebhookRequest ratingsParameters)
{
_log.LogTrace("Start obtain fundamentals");
var companyNameToResolve = ratingsParameters.QueryResult.Parameters[companyName].ToString();
var tickersToUse = await _obtainCompanyDetails.ResolveCompanyNameOrTicker(companyNameToResolve);
if (string.IsNullOrWhiteSpace(tickersToUse))
{
return(new WebhookResponse
{
FulfillmentText = $"Could not resolve {companyNameToResolve}"
});
}
var fulfillmentText = new StringBuilder();
try
{
int counter = 0;
foreach (var ticker in tickersToUse.Split(','))
{
var computedRatingMd = _connectionHandlerCF.Get().Where(x => x.Ticker.ToLower().Equals(ticker.ToLower()) &&
x.FYear == DateTime.Now.Year).FirstOrDefault();
if (computedRatingMd == null)
{
computedRatingMd = _connectionHandlerCF.Get().Where(x => x.Ticker.ToLower().Equals(ticker.ToLower()) &&
x.FYear == DateTime.Now.Year - 1).FirstOrDefault();
}
PiotroskiScore computedRating = null;
if (computedRatingMd != null)
{
computedRating = Mapper.Map <PiotroskiScore>(computedRatingMd);
}
fulfillmentText.Append(await BuildCompanyProfile(ticker, computedRating));
if (++counter >= 2)
{
break;
}
}
if (counter >= 2)
{
fulfillmentText.Append($"Limiting result set as the search term {companyNameToResolve} resolved to too many results.\n");
}
}
catch (Exception ex)
{
_log.LogCritical($"Error while processing Getting Company Ratings; \n{ex.Message}");
return(new WebhookResponse
{
FulfillmentText = Utilities.ErrorReturnMsg() + Utilities.EndOfCurrentRequest()
});
}
if (fulfillmentText.ToString().Contains("Piotroski"))
{
fulfillmentText.Append("Note: Piotroski F-Score is based on company fundamentals; " +
"a rating greater than 6 indicates strong fundamentals");
}
var webhookResponse = new WebhookResponse
{
FulfillmentText = fulfillmentText.ToString()
};
_log.LogTrace("End obtain fundamentals");
return(webhookResponse);
}
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);
}
}
