/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$this->info("This is involves downloading and storing several million records. This may take several hours...");
if ($this->confirm('Do you wish to continue?')) {
$this->info("Downloading historical financials...");
$numberOfStocks = Stock::count();
foreach (Stock::lists('stock_code') as $key => $stockCode) {
if (!Historicals::where(['stock_code' => $stockCode, 'date' => '2015-08-11'])->first()) {
$historicalSheetUrl = "http://real-chart.finance.yahoo.com/table.csv?s=" . $stockCode . ".AX&d=7&e=12&f=2015&g=d&a=7&b=11&c=2015&ignore=.csv";
if (get_headers($historicalSheetUrl, 1)[0] == 'HTTP/1.1 200 OK') {
file_put_contents('database/files/spreadsheet.txt', trim(str_replace("Date,Open,High,Low,Close,Volume,Adj Close", "", file_get_contents($historicalSheetUrl))));
$spreadSheetFile = fopen('database/files/spreadsheet.txt', 'r');
$dailyTradeRecord = array();
while (!feof($spreadSheetFile)) {
$line = fgets($spreadSheetFile);
$pieces = explode(',', $line);
array_push($dailyTradeRecord, array('stock_code' => $stockCode, 'date' => $pieces[0], 'open' => $pieces[1], 'high' => $pieces[2], 'low' => $pieces[3], 'close' => $pieces[4], 'volume' => $pieces[5], 'adj_close' => $pieces[6], 'created_at' => date("Y-m-d H:i:s"), 'updated_at' => date("Y-m-d H:i:s")));
}
//\DB::table('historicals')->where('stock_code', $stockCode)->delete();
\DB::table('historicals')->insert($dailyTradeRecord);
}
}
$this->info("Completed: " . $stockCode . " " . ($key + 1) . "/" . $numberOfStocks . " - " . round(($key + 1) * (100 / $numberOfStocks), 2) . "%");
}
$this->info("The historical financials have been downloaded.");
}
}
public function testStockGraph()
{
$mostRecentDateInGraph = Historicals::getMostRecentHistoricalDate('TLS');
$mostRecentMonth = jdmonthname(explode("-", $mostRecentDateInGraph)[1], 2);
$mostRecentYear = explode("-", $mostRecentDateInGraph)[0];
$this->visit('/stockGraph/TLS/last_month/Price')->see('Date')->see('Price')->see($mostRecentMonth)->see($mostRecentYear);
$this->visit('/stockGraph/TLS/all_time/Price')->see('Date')->see('Price')->see('Feb 1, 2000')->see($mostRecentMonth)->see($mostRecentYear);
}
private static function alreadyFilled($stockCode)
{
$mostRecentRecord = Historicals::where('stock_code', $stockCode)->orderBy('date', 'desc')->first();
if ($mostRecentRecord->fifty_day_moving_average == 0.0 || $mostRecentRecord->two_hundred_day_moving_average == 0.0) {
return false;
}
return true;
}
private static function getStockCodeParameter($testMode = 'false')
{
if ($testMode != 'true') {
date_default_timezone_set("Australia/Sydney");
//Limit of 100 at a time due to yahoo's url length limit
$stockCodeList = Stock::whereNotIn('stock_code', Historicals::distinct()->where('date', date("Y-m-d"))->lists('stock_code'))->take(100)->lists('stock_code');
$stockCodeParameter = "";
foreach ($stockCodeList as $stockCode) {
$stockCodeParameter .= "+" . $stockCode . ".AX";
}
return substr($stockCodeParameter, 1);
} else {
return "TLS.AX+CBA.AX";
}
}
/**
* Display the specified resource.
*
* @param int $id
* @return Response
*/
public function show(Request $request, $id)
{
if ($request->stockCodeFind) {
return redirect('stocks/' . $request->stockCodeFind);
}
if (Stock::where('stock_code', $id)->first()) {
$priceGraphData = Stock::getGraphData($id, 'last_month', 'Price');
$prices = \Lava::DataTable();
$prices->addStringColumn('Date')->addNumberColumn('Price')->addNumberColumn('50 Day Moving Average')->addNumberColumn('200 Day Moving Average')->addRows($priceGraphData);
$stockPriceLava = \Lava::LineChart('StockPrice')->dataTable($prices)->customize(['explorer' => ['actions' => ['dragToZoom', 'rightClickToReset']]])->setOptions(['width' => 620, 'height' => 360, 'title' => 'Price of ' . strtoupper($id)]);
$sector = Stock::where('stock_code', $id)->pluck('sector');
$motRecentSectorHistoricalsDate = SectorHistoricals::getMostRecentSectorHistoricalsDate();
return view('pages.individualstock')->with(['stockPriceLava' => $stockPriceLava, 'stock' => Stock::where('stock_code', $id)->first(), 'relatedStocks' => StockMetrics::getMetricsByStockList(Stock::getRelatedStocks($id), 'omit'), 'metrics' => StockMetrics::where('stock_code', $id)->first(), 'mostRecentStockHistoricals' => Historicals::where('stock_code', $id)->orderBy('date', 'DESC')->limit(1)->first(), 'sectorAverage' => SectorHistoricals::where(['sector' => $sector, 'date' => $motRecentSectorHistoricalsDate])->first()]);
}
return redirect('/');
}
public static function getGraphData($stockCode, $timeFrame = 'last_month', $dataType)
{
$historicals = Historicals::where(['stock_code' => $stockCode])->dateCondition($timeFrame)->orderBy('date')->get();
$graphData = array();
foreach ($historicals as $record) {
if ($dataType == 'Price') {
$price = $record->close;
$fiftyDayMovingAverage = $record->fifty_day_moving_average;
$twoHundredDayMovingAverage = $record->two_hundred_day_moving_average;
}
array_push($graphData, array(getCarbonDateFromDate($record->date)->toFormattedDateString(), $price, $fiftyDayMovingAverage, $twoHundredDayMovingAverage));
}
//Add Current day's trade value to graph data
//10:32am allows time for the metrics to be populated
if (isTradingDay() && getCurrentTimeIntVal() >= 103200 && !Historicals::where(['stock_code' => $stockCode, 'date' => date('Y-m-d')])->first()) {
$stockMetric = StockMetrics::where('stock_code', $stockCode)->first();
$metricDate = explode(" ", $stockMetric->updated_at)[0];
array_push($graphData, array(getCarbonDateFromDate($metricDate)->toFormattedDateString(), $stockMetric->last_trade));
}
return $graphData;
}
private static function calculateDayGain($listOfStocks, $sectorName, $providedDate)
{
$marketCaps = array();
$marketCapDayChanges = array();
foreach ($listOfStocks as $stock) {
$marketCap = StockMetrics::where('stock_code', $stock)->pluck('market_cap');
$open = Historicals::where('stock_code', $stock)->where('date', $providedDate)->pluck('open');
$close = Historicals::where('stock_code', $stock)->where('date', $providedDate)->pluck('close');
$dayChange = FillSectorHistoricalsCommand::getDayChange($open, $close);
array_push($marketCaps, $marketCap);
array_push($marketCapDayChanges, $marketCap - $marketCap / ($dayChange / 100 + 1));
}
$totalSectorMarketCaps = array_sum($marketCaps);
$totalSectorDayChange = array_sum($marketCapDayChanges);
if ($totalSectorMarketCaps > 0) {
$percentChange = 100 / $totalSectorMarketCaps * $totalSectorDayChange;
} else {
$percentChange = 0;
}
SectorHistoricals::updateOrCreate(['sector' => $sectorName, 'date' => $providedDate], ['sector' => $sectorName, 'date' => $providedDate, 'day_change' => round($percentChange, 2)]);
}
public static function getMostRecentHistoricalDate($stockCode)
{
return Historicals::where('stock_code', $stockCode)->orderBy('date', 'desc')->take(1)->lists('date')[0];
}
private static function getFirstTradingDateOfYear()
{
$firstTradingRecordOfYear = Historicals::where('date', 'like', date("Y") . '-01-0%')->orderBy('date')->limit(1)->get();
foreach ($firstTradingRecordOfYear as $record) {
return $record->date;
}
}
