Transactional Data from Investment Account

So.... there are impending law-related proceedings that I'm going through where I have to provide certain values of financial assets that I hold, what their values were over the years, etc.  I have an investment account at what's now called CapitalOne Investing (was ShareBuilder when I signed up).  I was told that I could call them up and tell them a date or two and they could send me the value of my brokerage account on those dates.

They could not do this...

However, they do provide data on every transaction that you performed or which was performed on your behalf over the course of those 8 or so years I've been investing. This data is in CSV format.... Programming to the rescue!!

I used Go of course.

The data is in this format:

Date,Action,Security,Price,Quantity,Amount,Text,Memo,Commission

With the important data being Date, Action, Security, Price, Quantity (for what I care about).

The other part of this is historical stock price data which was easy enough to find on a site like MarketWatch.

The design of my program is like this.

  1. Read in the data.
  2. Pick a Start and End date for iteration
  3. Build a Map of Transaction list by Date. So in go this would be map[time.Time]data.LineList     (with a single item in data.LineList just holding a CSV line)
  4. Iterate over the dates, adding 1 to day on each iteration, running through the transactions for that day if they exist, otherwise move the the next one.
  5. On 5 separate transaction types, perform an action
    1. Buy, ReinvDiv - Peform a buy with (Date, Stock Symbol, Quantity, Price)
    2. Sell - Perform a sell with (Date, Stock Symbol, Quantity, Price)
    3. ShrsOut - Perform a SharesOut call. This is the first part of a reverse stock split.
    4. ShrsIn - Perform a SharesIn call. This is the second part of a reverse stock split.  (My transaction history only had reverse stock splits, not regular stock splits)
  6. Over the course of the years, keep track of shares owned, and on specific dates, output the value of the entire portfolio.

I was able to run this, then tabulate how many stocks I owned on the dates in question, then look up their price in the historical data on MarketWatch. Here some of the code, which will show how it works and make the data structure seem pretty apparent...

func (port *Portfolio) Buy(date time.Time, symbol string, quantity, price float64){
    if _,ok := port.Holdings[symbol]; ok {
        h := port.Holdings[symbol]
        h.Quantity += quantity
        h.CostBasis += quantity * price
        port.Holdings[symbol] = h
    } else {
        h := Holding { Symbol: symbol, Quantity: quantity, CostBasis: quantity * price }
        port.Holdings[symbol] = h
    }
}

func (port *Portfolio) Sell(date time.Time, symbol string, quantity, price float64){
    if _,ok := port.Holdings[symbol]; ok {
        h := port.Holdings[symbol]
        h.Quantity += quantity
        h.CostBasis += quantity * price
        port.Holdings[symbol] = h
    }
}

// stock reverse / splits
func (port *Portfolio) SharesOut(date time.Time, symbol string, quantity float64){
    if _,ok := port.Holdings[symbol]; ok {
        h := port.Holdings[symbol]
        h.Quantity += quantity
        port.Holdings[symbol] = h
    }
}

func (port *Portfolio) SharesIn(date time.Time, symbol string, quantity float64){
    if _,ok := port.Holdings[symbol]; ok {
        h := port.Holdings[symbol]
        h.Quantity += quantity
        port.Holdings[symbol] = h
    }    
}

Quantity was negative on the Sell so the code could have been the same, almost.

Here's the process data function. As usual, I am a student of Go, if there's a better way to do any of this, I will be happy to learn about it!!

func processData(allData data.LineList, start time.Time, dates ...time.Time) *data.Portfolio {
    port := data.NewPortfolio()

    dateMap := getDateMap(allData)

    dt := start
    end := time.Now()

    for dt.Unix() < end.Unix() {
        impDate := false
        for _,d := range dates {
            if d == dt {
                impDate = true
            }
        }

        for _, lineItem := range dateMap[dt] {
            switch lineItem.Action {
                case "Buy", "ReinvDiv":
                    port.Buy(lineItem.Date, lineItem.Security, lineItem.Quantity, lineItem.Price)
                    break
                case "Sell":
                    port.Sell(lineItem.Date, lineItem.Security, lineItem.Quantity, lineItem.Price)
                    break
                case "ShrsOut":
                    port.SharesOut(lineItem.Date, lineItem.Security, lineItem.Quantity)
                    break
                case "ShrsIn":
                    port.SharesIn(lineItem.Date, lineItem.Security, lineItem.Quantity)
                    break
            }
        }

        if impDate {
            fmt.Println(dt.Format("Jan 2, 2006"), port)
        }

        dt = dt.AddDate(0, 0, 1)
    }

    return port
}

Mortgage Account Finances

I've come up with a brilliant idea. So I started the refinance process on my mortgage to bring down my monthly payments. I'm pretty happy with where they'll be for the next 30 years. But I've come up with a concept, at least, according to my outside influence, I've come up with it on my own, but it could be a concept already.

If I'm generally tight on finances, even with considering my mortgage payment is a lot less than it was, if I saved up in a dedicated account, enough for one mortgage payment, it would be like, after months of saving, it would be a huge release of pressure on the burden of paying mortgage that month.

If my mortgage payment is (fictitious amount here) $1200 a month, which includes all taxes and escrow, etc. Let's say I want that magical burden-lifting event to happen twice a year. I guess logically (although I came to the number in a different way) you'd multiply that mortgage by 2, to get $2400. Then divide that by 52 weeks in the year. I simply did $1200 / 26. You'd get 46.16, rounded up to the nearest penny.

$46.16 is the amount that you have to put away each week in order to have $1200 in a savings account after 6 months, that you can transfer over to your checking and use to pay your mortgage that month. Then it's like getting a cash injection. As a bonus you'd also have some extra cash in there due to interest accruing on that account over 6 months!

Obviously this isn't a long term savings plan. You'd be doing those as well on the side. I've got betterment for that. This would just be an extra account in my bank that I auto transfer $46.16 to every Friday (in my case). But I'm also transferring money to my investment accounts at the same time. It might be the reason I'm a little tight in finances, but in 6 months, that will be amazing to have that extra injection of cash!  :)  I don't do finance, just computers, so I don't know the terms for this type of financial strategy, but there probably is one.

Happy Saving!