Search

Extraction of Cooperative.com 401(k) Share Prices

2013 is upon us; HR says it’s time to look at my cooperative retirement account.  I get quarterly statements informing me of my investment performance along with the share price of each investment.  This is a good, generalized summary for a retirement account such as a 401(k).

MONEY MARKET FUND

$2.96776

$2.96776

01/14/2013

SHORT-TERM BOND FUND

$13.01035

$13.00820

01/14/2013

GROWTH & INCOME STOCK FUND

$43.13542

$43.00305

01/14/2013

S&P 500 STOCK INDEX FUND

$4.55544

$4.55030

01/14/2013

SMALL COMPANY STOCK FUND

$21.20406

$21.09254

01/14/2013

INTERNATIONAL STOCK FUND

$19.14125

$19.19277

01/14/2013

DIVERSIFIED GROWTH STOCK FUND

$6.51659

$6.52518

01/14/2013

RETIREMENT TARGET DATE PORTFOLIO

$12.39512

$12.37890

01/14/2013

2020 TARGET DATE PORTFOLIO

$11.88915

$11.87494

01/14/2013

2030 TARGET DATE PORTFOLIO

$11.70019

$11.68705

01/14/2013

2040 TARGET DATE PORTFOLIO

$11.51616

$11.49763

01/14/2013

Looking at this report, I can see that I am on-track and everything is performing to my annual expectations.  Lucky for me, I didn’t hold any of the poorly performing funds. But what if I did?  Should I move my investments contributions around or re-allocate?  I need more information than just what is on my most recent quarterly report.  It is conceivable that a “poor” fund is showing signs of turning around if I could get more detail.  What’s the share price… today, yesterday, or a year ago?

On to Cooperative.com…

Clicking on My Retirement, 410(k) Investment Details, Investment Information, I can view today’s and historical share prices by entering a date and pressing the Find button.  This is a good start.  After fooling around with the interface, I couldn’t find a way to download all the daily prices. I would like to see a trend chart for each fund.

shares1

Instead of beginning the tedious effort in copying and pasting dates and share prices into Excel, I turned tothe iMacros data extractor to do all the heavy lifting for me.  The process is as follows:

FOR X=1 to 360

Input todays date – X
Press find
Extract the current share prices listed on the screen
Append extracted to a CSV file

NEXT X

This gave me a file of everything I’m looking for saved as c:\SHARES.csv

TAB T=1
TAB CLOSEALLOTHERS
'While logged into Cooperative.com, start this macro on this URL GOTO=https://benefits.cooperative.com/neb/pages/navigator/navigator.aspx?transaction=InvestmentInformation
FRAME NAME=PDSiFrame
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:GetPrices ATTR=ID:FPriceDate CONTENT=EVAL("var today = new Date(); var tomorrow = new Date(); tomorrow.setDate(today.getDate() - {{!LOOP}}); var day = tomorrow.getDate().toString(); if (day.length < 2) {day = \"0\" + day;} var month = (tomorrow.getMonth() + 1).toString(); if (month.length < 2) {month = \"0\" + month;} var year = tomorrow.getFullYear().toString(); var dateString = month + \"/\" + day + \"/\" + year; dateString;")
TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:GetPrices ATTR=VALUE:Find
TAG POS=2 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=6 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=10 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=14 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=18 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=22 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=26 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=30 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=34 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=38 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=42 TYPE=FONT ATTR=CLASS:maincontent EXTRACT=TXT
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:GetPrices ATTR=ID:FPriceDate EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\ FILE=SHARES.CSV

(Note: Copy the above code and paste into notepad or iMacros to view the code in its entirety)

Run this by pressing Play (Loop) with 365 set as the Max parameter.  You can set the Max to a higher value if you need more historical share price data.

shares2

Next, I imported the CSV into Excel and turned to the fancy pivot chart for some date-driven analysis:

shares3

For those that just want the share prices, here they are.  It’s not like they are top secret and we all shouldn’t have to run the script to peg out cooperative.com’s web server:

Read the rest of this entry »

Advertisements