Advanced Windows PowerShell Scripting Video Training

Advanced Windows PowerShell Scripting Video Training
Advanced Windows PowerShell Scripting Video Training

Monday, January 21, 2013

Use PowerShell to Rebalance Your Portfolio

Years ago I was introduced to portfolio rebalancing by a friend that I used to work with.  In short, portfolio rebalancing allows you to sell off some stock in funds that have increased in value, and then buy more shares in funds that have decreased in value.  This allows you to sell high, buy low.  The company that currently manages my business retirement plan does not have an option for this, but I am allowed to transfer funds between different investments without fees.

Below is an example of two concepts that I use in my PowerShell classes.  One is how to gather information from the internet and the other is how to use the XML variable type in PowerShell.  You will have to create a CSV file that contains three pieces of information.  FUND, QUNATITY, ALLOCATION.  The Fund will be the ticker symbol for a stock or mutual fund.  The quantity will be how much of that fund you own.  The allocation is a percentage of how much of your portfolio you want that investment to consume.  Make sure the allocation column totals 100%.

Function Rebalance-Portfolio
{
Param (
    [Parameter(Position=0, Mandatory=$TRUE, ValueFromPipeline=$TRUE)]
    [STRING]$Portfolio
)
#-- Support Functions ---------------------------------------------------------
 function Get-StockInfo
{
       [cmdletbinding()]
       param ($Sym)
      
       $Data = @()
       $Data += "Symbol,Name,Ask,Low,High,Low52,High52,Volume,DayChange,ChangePercent"
       $URL = "http://finance.yahoo.com/d/quotes.csv?s=$Sym&f=snl1ghjkvw4P2"
       $Data += Invoke-RestMethod -Uri $URL
       $Data += "`n"
       $Data = $Data | ConvertFrom-csv
       Write-Output $Data
      
      
} # END: function Get-StockInfo


#-- End Support Functions -----------------------------------------------------

    # Import the portfolio file.
    Try
    {
        $Funds = Import-csv -Path $Portfolio
    }
    Catch
    {
        Write-Host "Data file not found." `
         -ForegroundColor Red `
         -BackgroundColor DarkRed
         BREAK
    }

    # Object to hold stock information
    $StockInfo = @()

    # Total value of the portfolio
    [Single]$TotalValue = 0

    # Populate an object for each fund.
    ForEach ($Fund in $Funds)
    {
            $Symbol = $Fund.Fund
            #$Info = Get-FundQuote -TICKER $Symbol
            $Info = Get-StockInfo -Sym $Symbol
            $Obj = New-Object -TypeName PSObject
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "Fund" `
                -Value $Info.Symbol
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "Quantity" `
                -Value $Fund.Quantity
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "PreviousClose" `
                -Value $Info.Ask
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "CurrentValue" `
                -Value ([Single]$Fund.Quantity * [Single]$Info.Ask)
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "Allocation" `
                -Value $Fund.Allocation
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "AllocationGoal" `
                -Value 0
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "ChangeAmount" `
                -Value 0
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "ChangeShares" `
                -Value 0
            $Obj | Add-Member -MemberType NoteProperty `
                -Name "NewShareBalance" `
                -Value 0
        
          $TotalValue += ([Single]$Fund.Quantity *[Single]$Info.Ask)
          $StockInfo += $Obj

         
    }

    # Figure out allocations.
    ForEach ($Stock in $StockInfo)
    {
        $Stock.AllocationGoal = "{0:N2}" -f ($TotalValue*([Single]$Stock.Allocation*.01))
        $Stock.CurrentValue = "{0:N2}" -f $Stock.CurrentValue
        $Stock.ChangeAmount =  $Stock.AllocationGoal - $Stock.CurrentValue
        $Stock.ChangeShares = "{0:N3}" -f ($Stock.ChangeAmount/$Stock.PreviousClose)
        $Stock.NewShareBalance = "{0:N3}" -f ([SINGLE]$Stock.Quantity + [SINGLE]$Stock.ChangeShares)
   
    }
    $StockInfo | FT -AutoSize
<#
.SYNOPSIS
Determines fund rebalancing amounts.

.DESCRIPTION
Returns the mutual fund reallocation amounts provided through a CSV file.  This
allows the user to properly rebalance an investment portfolio.  The CVS
file format is:
Fund, Quantity, Allocation

FUND: The ticker symbol of the mutual fund.
QUANTITY: The current quantity of the fund held.
ALLOCATION: The percentage that this fund should be of the total portfolio
if the portfolio was balances.

Once executed, the ChangeAmount property well tell the user how many dollars
of a fund should be purchased (Positive amount) or sold (Negative amount) in
offer to balance the portfolio.

Fund: One of your mutual funds.
Quantity: The amount of shares that you own.
PreviousClose: The last price of these shares.
CurrentValue: How much of the fund you own in dollars.
Allocation: A percentage of how much of your portfolio should be invested in
            this fund.
ChangeAmount: The number of dollars you need to buy or sell to bring this
              fund into balance.
ChangedShares: The number of shares you would have to buy or sell to bring
               this fund into balance.
NewShareBalance: The number of shares you need in each fund to bring them
                 into balance.

.PARAMETER Portfolio
The fill path of the CSV file containing the users portfolio data.  Referred
to the help file description for the proper format.

.EXAMPLE
Rebalance-Portfolio -Portfolio "C:\Users\Jason\Documents\PowerShell\Code\Portfolio2.csv"

Fund  Quantity PreviousClose CurrentValue Allocation AllocationGoal ChangeAmount ChangeShares NewShareBalance
----  -------- ------------- ------------ ---------- -------------- ------------ ------------ ---------------
USAUX 290.769  33.51         9,511.05     25         8,757.31               -754 -22.501      268.268      
USCGX 383.996  7.03          2,638.05     5          1,751.46               -887 -126.174     257.822      
USEMX 353.756  17.98         6,236.72     15         5,254.39               -983 -54.672      299.084      
USGRX 294.151  16.57         4,759.36     15         5,254.39                495 29.873       324.024      
USAAX 374.512  17.30         6,321.76     20         7,005.85                684 39.538       414.050      
USISX 408.992  13.91         5,562.29     20         7,005.85               1444 103.810      512.802      

The ChangeAmount column tells you the dollar amount of shares that need to be
bought or sold for each fund in order to bring your portfolio into
balance.

#> 
}

No comments: