A Case of the Probabilities

We're all familiar with the random walk hypothesis and how it relates to the market.  It's a pretty well-known theory that the market moves randomly; that is, you can't predict if a stock price's next move will be up or down.  But that doesn't keep us from making educated guesses.

I don't have long, so I'll keep it short.  I spent the majority of my morning retrieving tick data for Pfizer and compiling it into one location, I think I ended up with about 150,000 data points.  I then took the first 40,000 data points and fitted them with logical statements:

• If my data point increased from the previous it get's a one, otherwise zero
• If my data point decreased from the previous it get's a one, otherwise a zero
• If my data point remains the same from the previous it get's a one, otherwise a zero

From here I was able to find the probability of increase, decrease, and no change.  As one would assume the probabilities for increase and decrease were incredibly close (I think they deviated by .0001% most the time -- I'm not sure, my data is on a computer across town). 

This created a nice reference point; since my long-run probability of an increase is X and my short-run probability of an increase is <X; this indicates that my short-run average would begin to increase in order to bring the my long-run average back to X.  That is, every time the probability of a price increase fell below the long-run average, we begin to see future probabilities increase in order to maintain that long-run average.  And that's exactly what happened. 

Every time my most recent 10,000 data points fell below the long-run average I placed a buy marker and sold on the next tick. I simulated this for 7 days (September 6 - September 14). The results were quite pleasing:

• Each day resulted in a gain
• After 7 days I ended up with 103.85% of my starting capital

This has it's limitations though:  

1. This process depends on speed, each buy must be on that tick and each sell on the very next; I'm not great with computers -- actually, I'm not even okay with computers -- so I don't know the power necessary to fulfill these requirements. 
2. Fees will destroy me. I either need large sums of money to reduce my fees per share or I need to find a brokerage that takes a percentage of my gains.  Preferably the former. 

I'm also going to need to add in safety nets, here's what I'm thinking: 

• Fit my data to a distribution curve (don't know which yet) and find my standard deviations.  If my standard deviation falls below 1 to the left it becomes a buy. 
• Create a conditional probability P( increase on next tick | current tick falls below one standard deviation).  From here I can compare across companies and pick the one that gives me the greatest probability of an increase. 

I still have work to do on this, but it's an interesting hypothesis none the less. Further testing is required, but beforehand I must a) gather more data b) apply this hypothesis to more than one company ( i.e. all the S&P 500) and c) write a program that can run my simulation. 

You will see more of this in my coming posts. 

Intraday Data Code

Through my efforts to study the stock market's technicalities I have found my self in need of more and more data.  It's easy for me to go to the Wall Street Journal and download historical data, but that's not the problem.  The problem is finding more frequent data — intraday data.

My quest to find intraday data on Google proved fruitless.  I didn't trust any of those sketchy websites and I I refused to pay any amount of money to data collectors; so, I brought it upon myself to write something that could help me.

At this point in time I have not formal training with writing code, I am purely self taught.  As a consequence my code is quite limited and clumsy, but after a few weeks I was able to pull something together, much in thanks to various forums and trial and error.

My code is written in Visual Basic for Applications (VBA) which can be found in almost all Excel programs.  Like I said, it's quite the round-about but it gets me what I need. This code was built with Windows XP and IE 8.0.6 (I'll try to make this as easy to read as possible):

Before we get started, insert this set of code into module2

'This will search for, find, and USD (explination later)

Sub Macro2()
' Macro2 Macro
    Windows("Html_Dump.txt").Activate
    Cells.Find(What:="USD", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Copy

End Sub


'This will search for, find, and EDT (explination later)

Sub Macro3()
' Macro3 Macro
    Windows("Html_Dump.txt").Activate
    Cells.Find(What:="edt", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Copy
End Sub

Now for the meat of the code

Sub GetTextOrHtmlFromIe()

'This code works with IE 8.0.6 and you'll have to enable Microsoft HTML Object Library. 

'http://www.mrexcel.com/forum/showthread.php?342939-How-to-save-HTML-source-code-from-VBA/page2
'Much of my code came from this website, atleast the part that goes to IE and grabs the data
'from the WSJ HTML code. What it does is extracts all the HTML code and dump it onto an Excel
'Document

Dim bln As Boolean
Dim ie As Object, objDoc As Object
Dim c As Object

'You'll have to either change the realtime.xlsm to your desired file name (the file
'that holds this code) or save your workbook as realtimequote.xlsm. The "a1:A740" is a
'counter of sorts that lets my code continuously loop throughout the data.  So since this 
'code grabs information every 30 seconds and let my code run for 6 hours, we get 6*60*2 = approx 740
'Also, every time you run this code make sure cells a1:a740 or whatever you end at are cleared. 

For Each c In Workbooks("Realtime.xlsm").Sheets("sheet1").Cells.Range("a1:a740")
    If c = 0 Then

        Const strMsg As String = "To get a text version of your page, click [Yes]," & vbLf & _
        "To get the Html version, click [No]"

'Like I said, I grab my information from the WSJ, so the stock info I'm grabbing is Pfizer,
'if you want another stock change the pfe to your desired ticker symbol

        Const strURI As String = "http://quotes.wsj.com/pfe"
        Set ie = CreateObject("internetexplorer.application")
        ie.Navigate strURI

'Wait for page to loa
        Do
            If ie.ReadyState = 4 Then
                ie.Visible = False
            Exit Do
            Else
                DoEvents
            End If
        Loop

'This is going to pause the application for 20 seconds, it more or less is just making sure my page
'is fully loaded before I grab the HTML code, it always takes up a good portion of my codes wait time,
'i figure this is as good a time as any.

        Application.Wait (Now() + TimeValue("00:00:20"))

'So after I grab my HTML code from Internet Explorer I dump it into an Excel Workbook.  I call this
'workbook HTML_Dump and save it on my C drive.  You can change the filepath if you'd like,
'http://excelexperts.com/VBA-Tips-Import-A-Text-File

        Set objDoc = ie.Document

        Filename = "C:\Html_Dump.txt"
            FileNo = FreeFile
            Open Filename For Binary Access Write As #FileNo
            Put #FileNo, , objDoc.body.innertext
            Close #FileNo
            
        Set objDoc = Nothing
        Set ie = Nothing

        On Error Resume Next

        Set mytextfile = Workbooks.Open("C:\html_dump.txt")

'when the text doucment gets dumped into excel it doesnt seperate all the text.  In otherwords, one line is one cell, 
'so what I have to do is search for a string of unique characters of my desired line, copy, and paste. I do this by 
'calling modules. After moduels are called I use the "text to column" excel function and deliminate to seperate
'all the words, numbers, etc. 

'This module is for a newer version of IE (I'm currently working on it).

        ''Call Module2.Macro1

        ''c.PasteSpecial
        ''Workbooks("Realtime.xlsm").Activate
        ''Application.CutCopyMode = False
        ''Selection.TextToColumns Destination:=c, DataType:=xlDelimited, _
            ''TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            ''Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            '':=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            ''Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ''), Array(14, 1), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True
        ''c.Offset(0, 2).Copy
        ''c.Offset(0,1).PasteSpecial
        ''c.Offset(0, 1).ClearContents
        ''c.Offset(0, 2).ClearContents
        ''c.Offset(0, 3).ClearContents
        ''c.Offset(0, 4).ClearContents
        ''c.Offset(0, 5).ClearContents

        ''On Error Resume Next

'Calling module two, going text to column, and deliminating

        Call Module2.Macro2

        c.Offset(0, 1).PasteSpecial
        Workbooks("Realtime.xlsm").Activate
        Application.CutCopyMode = False
        Selection.TextToColumns Destination:=c.Offset(0, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ), Array(14, 1), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True
        c.Offset(0, 2).Copy
        c.Offset(0, 1).PasteSpecial
        c.Offset(0, 2).ClearContents
    
'This is for an older version of Windows IE(8.0.6001), may not have to do it on a newer one. In this version
'the price is attached to the USD tag, so it's like 24.75USD -- this deliminates it so we can grab
'only the price
    
        c.Offset(0, 1).Select
        Selection.TextToColumns Destination:=c.Offset(0, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="U", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
        c.Offset(0, 13).Copy
        c.Offset(0, 2).PasteSpecial
        Worksheets("sheet1").Cells.Range("D:S").ClearContents

        On Error Resume Next

'Again, same set of rules but calls module3

        Call Module2.Macro3
  
        c.Offset(0, 3).PasteSpecial
        Workbooks("Realtime.xlsm").Activate
        Application.CutCopyMode = False
        Selection.TextToColumns Destination:=c.Offset(0, 3), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ), Array(14, 1), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True
        c.Offset(0, 10).Copy
        c.PasteSpecial
        Worksheets("sheet1").Cells.Range("d:S").ClearContents
    
        On Error Resume Next

'This closes HTML_dump and deletes it from your computer
    
        mytextfile.Close False
        Kill "c:\HTML_dump.txt"

'This part destroys everything IE (more or less just shuts the program down), so if you're using IE while this code is running
'you will find yourself very irritated. It'll save you RAM by closing all IE background windows. 

        'sledgehammer approach to closing ALL ie windows.
        'http://www.mrexcel.com/forum/showthread.php?t=229394
    
        Dim objWMI As Object, objProcess As Object, objProcesses As Object
        Set objWMI = GetObject("winmgmts://.")
        Set objProcesses = objWMI.ExecQuery( _
        "SELECT * FROM Win32_Process WHERE Name = 'iexplore.exe'")
        For Each objProcess In objProcesses
            Call objProcess.Terminate
        Next
        Set objProcesses = Nothing: Set objWMI = Nothing
    
'This makes the application wait 5 seconds.  So, this 5 seconds plus the 20 seconds above equates a total of 25 seconds of wait time
'plus the (very generously estimated) 5 seconds to let the code run. All in all this takes about 30 seconds to run. 

        Application.Wait (Now() + TimeValue("00:00:15"))

    End If
Next c

End Sub

There are definitely things I could improve on.  For one, I can only do one ticker symbol at a time; which causes a problem, especially when I need to compare data (hard to compare if you have nothing to compare it to).  It would also be nice to add  GUI that gave me fields for parameters and what not.  This is definitely no finished project, but it is the backbone of whats to come.

Introduction

This blog is going to be a bit different than the rest; it will be a place where I can organize my work and create a portfolio. The idea came from my brother, saying how useful it is for prospective employers looking to judge your competence.  So, that's what this blog will be - my most valued and thought provoking work.

But first, a little about me.  My name is Zack Miller and I'll be starting my fourth of five years at the University of Iowa.  I attend the Tippie College of Business and am pursuing a degree in finance, economics, and mathematics with a minor in computer science.

Quite the list, huh? Well, had I been smarter in my earlier years I could graduate on-time with everything I needed.  It wan't until last year I was introduced to a mathematical finance program that peaked my interest.  Finally I had found something that tied in my love for quantitative reasoning and financial markets.  My goal is to attend a top-tier mathematical finance program and the only way for me to build a strong base is to load up on mathematics and computer science courses -- at this time I am nine-tenths the way through my finance and economics degree.  My fifth year will entail mostly mathematics and computer science.

So, investments?  Sounds pretty boring, almost like every other undergraduate finance major.  That's not quite true, my goal isn't to make money or pick a career that leads to an easy retirement.  Financial markets - any type of market for that matter -  are one of the most interesting philosophies humans have created.  We put so much weight on this hypothetical idea that we let it run our lives only to hope that we can reach sheer happiness when we finally get everything we need, often times to the point of utter destruction.

I want this idea gone. Every market should be free of resistance and barriers. Technology has come such a long way that many of our financial ideals of the 1930s, 40s, 50s, 60s, 80s, 90s have all become irrelevant. All of our money, products, and resources should flow without disruption, - of course we need some but I'll save you from my political ideologies - and at that point I strongly believe humans can have their cake and eat it too.

With that being said, this blog will house everything related to this field.  There may be times where I post code, company analyses, papers, or articles.  I may even throw in the occasional vacation photo or two; but for now, you must wait.