# ******************************************************
# *
# * Name:         Final-Solution.ps1
# *     
# * Design Phase:
# *     Author:   John Miner
# *     Date:     08-21-2017
# *     Purpose:  Working with text files.
# *
# ******************************************************



#
# Name:      Get-WebRequest-Table
# Purpose:   Grab data from internet.
#

# http://www.leeholmes.com/blog/2015/01/05/extracting-tables-from-powershells-invoke-webrequest/

function Get-WebRequest-Table {
    [CmdletBinding()] 
    param(
        [Parameter(Mandatory = $true)]
        [String] $Url,

        [Parameter(Mandatory = $true)]
        [int] $TableNumber
    )

    # Make are web request
    $request = Invoke-WebRequest $url 

    # Extract the tables 
    $tables = @($request.ParsedHtml.getElementsByTagName("TABLE"))

    # Pick which table #?
    $table = $tables[$TableNumber]

    # Header data
    $titles = @()

    # Row data
    $rows = @($table.Rows)

    # Go through all of the rows in the table
    foreach($row in $rows)
    {

        # Get the cells
        $cells = @($row.Cells)
          

        # Found table header, remember titles
        if($cells[0].tagName -eq "TH")
        {
            $titles = @($cells | % { ("" + $_.InnerText).Trim() })
            continue
        }

        # No table header, make up names "P1", "P2", ...
        if(-not $titles)
        {
            $titles = @(1..($cells.Count + 2) | % { "P$_" })
        }

        # Ordered hash table output
        $output = [Ordered] @{}

        # Process each cell
        for($counter = 0; $counter -lt $cells.Count; $counter++)
        {
            # Grab a title
            $title = $titles[$counter]

            # Just incase we have an empty cell
            if(-not $title) { continue }

            # Title to value mapping
            $output[$title] = ("" + $cells[$counter].InnerText).Trim()
        }

        # Finally cast that hashtable to an object
        [PSCustomObject] $output

    }
}


# Console output (y - Continue, n - Silent)
$VerbosePreference = "Continue"

#
#  1 - Create directory 
#

# Debug line
Write-Verbose "Remove existing directory"

# Remove dir & all items
$path = "c:\stocks"
If((test-path $path))
{
Remove-Item -Recurse -Force $path
}

# Debug line
Write-Verbose "Create new directory"

# Create stocks dir
$path = "c:\stocks"
If(!(test-path $path))
{
New-Item -ItemType Directory -Force -Path $path
}

# Create inbound dir
$path = "c:\stocks\inbound"
If(!(test-path $path))
{
New-Item -ItemType Directory -Force -Path $path
}

# Create outbound dir
$path = "c:\stocks\outbound"
If(!(test-path $path))
{
New-Item -ItemType Directory -Force -Path $path
}



#
# 2 - Set working directory
#


# Debug line
Write-Verbose "Set working directory"

# Set directory
Set-Location "c:\stocks"



#
#  3 - Get S&P 500 list
#

# Debug line
Write-Verbose "Get S&P 500 list"

# Pull S&P 500 list from wikipedia
$site = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'
$input = Get-WebRequest-Table -Url $site -TableNumber 0

# Reformat data 
$output = $input | 
    Select-Object @{Name="Symbol";Expression={[string]$_."Ticker symbol"}}, 
        @{Name="Security";Expression={[string]$_."Security"}}, 
        @{Name="Sector";Expression={[string]$_."GICS Sector"}},
        @{Name="Industry";Expression={[string]$_."GICS Sub Industry"}},
        @{Name="Address";Expression={[string]$_."Address of Headquarters"}},
        @{Name="Date";Expression={[datetime]$_."Date first added"}};

# Save as csv file
$output | export-csv -NoTypeInformation -Path "c:\stocks\S&P-500.csv" -Delimiter ','



#
#  4 - Getting historical stock data
#

# Debug line
Write-Verbose "Start - Get historical data"
Write-Verbose " "

# Set working directory
Set-Location "c:\stocks"

# Read csv file
$list = Import-Csv -Path "S&P-500.csv" -Delimiter ',' -Encoding ascii | Sort-Object -Property symbol

# Period 1
$TimeSpan1 = [DateTime]'2016-01-02' - [DateTime]'1970-01-01';

# Period 2
$TimeSpan2 = [DateTime]'2017-01-01' - [DateTime]'1970-01-01';

# File counter
[int]$cnt = 0

# For each stock
foreach ($stock in $list)
{

    # Create variables used by proceess
    $symbol = $stock.symbol
    $site = 'http://finance.yahoo.com/quote/' + $symbol +'/history?period1=' + $TimeSpan1.TotalSeconds + '&period2=' + $TimeSpan2.TotalSeconds + '&interval=1d&filter=history&frequency=1d'

    $html = 'c:\stocks\inbound\' + $symbol + '-FY2016.HTML'
    $csv = 'c:\stocks\outbound\' + $symbol + '-FY2016.CSV'
    $json = 'c:\stocks\inbound\' + $symbol + '-FY2016.JSON'

    # Save web page 2 html file
    $response = (New-Object System.Net.WebClient).DownloadString($site)
    $response | Out-File -FilePath $html

    # Find outer document (json)
    $content = Get-Content $html
    foreach ($line1 in $content)
    {
        if ($line1 -match 'root.App.main')
        {
        $main = $line1
        }
    }

    # Remove java script tags
    $main = $main.Substring(16, $main.Length-17)

    # Find inner document (json)
    $start = $main.indexof('"HistoricalPriceStore"')
    $end = $main.indexof('"NavServiceStore"')
    $len = $end-$start-24
    $prices = $main.Substring($start+23, $len)

    # Write json document
    $prices | Out-File $json

    # Convert to powershell object
    $data = $prices | ConvertFrom-Json
    
    # Reformat data & remove dividends
    $output = $data.prices | 

    Select-Object @{Name="symbol";Expression={[string]$symbol}}, 
           @{Name="date";Expression={([datetime]"1/1/1970").AddSeconds($_."date").ToString("MM/dd/yyyy")}},
           @{Name="open";Expression={[float]$_."open"}}, 
           @{Name="high";Expression={[float]$_."high"}},
           @{Name="low";Expression={[float]$_."low"}},
           @{Name="close";Expression={[float]$_."close"}},
           @{Name="adjclose";Expression={[float]$_."adjclose"}},
           @{Name="volume";Expression={[long]$_."volume"}} |

    Where-Object {-not [string]::IsNullOrEmpty($_.volume) -and ($_.volume -ne 0) }


    # Open file
    $stream = [System.IO.StreamWriter]::new( $csv )

    # Write header
    $line2 = '"symbol","date","open","high","low","close","adjclose","volume"'
    $stream.WriteLine($line2) 

    # For each line, format data
    $output | ForEach-Object { 
        $line2 = [char]34 + $_.symbol + [char]34 + "," + [char]34 + $_.date + [char]34 + "," + 
            $_.open + "," + $_.high + "," + $_.low + "," + $_.close + "," + $_.adjclose + "," + $_.volume
        $stream.WriteLine($line2) 
    }

    # Close file
    $stream.close()


    # Status line
    $cnt = $cnt + 1

    # Debug line
    $line3 = "File Count: " + $cnt.ToString()
    Write-Verbose $line3

    $line3 = "File Name: " + $csv
    Write-Verbose $line3

    Write-Verbose " "
    
}

# Debug line
Write-Verbose "End - Get historical data"