Scheming Away

  • NBA Spreads

    • 26 Jan 2012
    • 0 Responses
    •  views
    • Betting NBA R SportsBetting
    • Edit
    • Delete
    • Tags
    • Autopost
    I'm a little curious about what is going on in the betting markets and perhaps someone can enlighten me.  If I look at the NBA expected home spreads over the last 5 years and compare them to the actual spreads we end up with a histogram that looks like something similar to the following.

    Spreads

    The tails of the actual results are fatter than the expected results but all in all I imagine that this is what we should expect when looking at these two data series.  However once you start looking at the ends of the tails something interesting occurs.

    Spread_difference_-11

    If we look at all games with an expected spread of -11 or greater and from the actual spread subtract the expected spread we on average end up with positive values.  This would mean that highly favored home teams on average are failing to beat the spread.  More importantly I suspect that they are failing to beat the spread 55% of the time +/- ~4%. That's well above the 52.4% win rate needed to earn money.

    I'm curious as to if anyone else has noticed this phenomenon or if I'm missing something very basic here.

    The output from the t-test from the spread difference when the home team is favored by 11 or more.

    One Sample t-test

mean(WinLose) = 0.5504451

data:  WinLose 
t = 28.706, df = 673, p-value < 2.2e-16
alternative hypothesis: true mean is not equal to 0 
95 percent confidence interval:
 0.5127946 0.5880956 
sample estimates:
mean of x

    • Tweet

  • RiskGen in VB.NET

    • 24 Jan 2012
    • 0 Responses
    •  views
    • VB excel excel-dna
    • Edit
    • Delete
    • Tags
    • Autopost
    Click here to download:
    RiskGen.7z (1.82 MB)

    I've been a little busy as of late, but I wanted to make sure that I got this out there.  Govert, from Excel-DNA, was kind enough to take the time to port my code over to VB.NET.  Hopefully, I'll have a bit of time soon to work on this further!
    • Tweet

  • RiskGen Test

    • 9 Jan 2012
    • 1 Response
    •  views
    • @risk excel excel-dna montecarlo vba
    • Edit
    • Delete
    • Tags
    • Autopost
    Click here to download:
    RiskGen.zip (181 KB)

    Well after building up a test concept for an open source @Risk in VBA I found that user definied functions (UDFs) from an XlAM link back to the original workbook.  Why this behavior was instituted I have no idea, but it allowed me to discover Excel-DNA. Excel-DNA allows you to create UDFs in any .Net language and expose it to Excel through the excel-dna XLL file.  IT was a shockingly simple process for someone who knows very little about both .Net, creating any sort of DLL, or Excel-DNA by following the tutorial on Ross McLean's excellent web site. 

    To that end I have created some *excellent* spaghetti code that recreates some basic functionality of @Risk. Simply download the zip file, extract, open the RiskGen XLL & XLAM.  Once they have been opened you can play with the RiskGen example by going to the RiskGen tab and clicking run simulation.  Currently it will generate some rough histograms and provide you with the output of your watch cells.  Watch cells are determined by adding the formula "+WatchOutPut()" to any cell you want to watch.  Stochastic inputs can be specified by bootstrapping (vlookups conditional upon a randbetween() linked to an index), or using functions such as "norm.inv(rand(),mean,stdev)".  The next step is to port it to a .Net language, move it to CodePlex, and try to find someone who is much better at programming than I to help with it.

    • Tweet

  • Quick VBA @Risk Update

    • 7 Jan 2012
    • 0 Responses
    •  views
    • @risk VBA excel
    • Edit
    • Delete
    • Tags
    • Autopost
    Click here to download:
    VBA @Risk.xlam (28 KB)

    I added a progress indicator to the status bar and ribbon launcher to facilitate running the macro. 
    • Tweet

  • Open Source @Risk

    • 7 Jan 2012
    • 0 Responses
    •  views
    • @risk Excel VBA finance simulation
    • Edit
    • Delete
    • Tags
    • Autopost

    Click here to download:
    VBA @Risk.xlam (19 KB)
    Click here to download:
    Example.xlsx (9 KB)
    (download)
    Click here to download:
    Example.xlsx (9 KB)

     

    These days any MBA, finance professional, or marketer worth thier salt is using some sort of risk analysis software to allow them not to only see the mean expected outcome of a project, but a range of outcomes based upon random variables and their interactions within a model.  In my MBA program we use @Risk extensively to value projects.  However, there is no guarantee that any company a prospective student may be joining has @Risk, Crystal Ball, or an equivalent software package.  This is especially true for smaller companies or independant investment professionals.

    To that end I've decided to start a project to copy many of the functions within @Risk.  What I would love to see is someone who actually knows how to program well, unlike myself, to help with the project.  To that end I've posted the XLAM for people to either play with or help out with.

    Currently to use the plugin you simply add the formula "+WatchOutput()" to any cell you want to monitor and then run the Main sub.  It'll ask how many iterations you want to run and then output the raw data of those cells through each iteration to a new sheet.  You can then create a histogram if you desire using the frequency function or look at the mean, standard deviation or whatever other factors.

    I've added an example file to show how it can be used for analysis given a normal distribution.  To run main you'll have to open the VBA editor using alt-F11 and run it manually.  Using the randbetween function this can be used for bootstrapping with some vlookups/index/hlookups and an index.
    • Tweet

  • Portfolio Weight Optimization

    • 21 Oct 2011
    • 0 Responses
    •  views
    • Excel Portfolio Solver Stocks VBA
    • Edit
    • Delete
    • Tags
    • Autopost
    Click here to download:
    Portfolio Optimization.xlsm (3.83 MB)

    I whipped up a little something that is an extension of what I learned in my MBA program.  Typically when constructing a portfolio and determine the correct weights you would maximize the Sharpe Ratio to give you the best risk return profile.  However, we have to ask ourselves if that is really the best scenario.  The Sharpe ratio will take into account both positive and negative moves, but all we really care about is downside risk.  We actually want upside risk.  So the question is how do we eliminate downside risk while maintaining our upside risk.  The answer is pretty simple we generate a bunch of portfolio returns based on past returns and determine what is the worst case scenario and then we maximize the worst case scenario (maximizing the worst case makes it better not worse). 

    All you have to do is load daily returns for a range of stocks into the data tab, must have returns for all dates loaded, and then we resample the data to determine 10,000 possible future scenarios that encompass 1 year of returns.  Once we have these returns we can then build a solver model that will maximize the minimum return.  Our expected return is then the average of the future estimated returns and our worst case scenario will be minimized.  I like this approach both because it is intuitive (no worrying about correlation matrices) and it makes a great deal of sense.  The correlation matrix will change over time and that isn't captured in the efficient frontier, but it will be captured with resampling.  Obviously past returns, even resampled ones, aren't an indicator of future performance but it does give us some insight into which portfolios may perform particularly poorly and which may perform particularly well.  Additionally, we don't have to make any assumptions about normal distributions and can simply plot our estimated 1 year returns.

    I would be interested to hear about the potential downsides of optimizing your portfolio in this fashion.
    • Tweet

  • Excel Solver & Options

    • 16 Oct 2011
    • 0 Responses
    •  views
    • Excel Options Solver
    • Edit
    • Delete
    • Tags
    • Autopost

    Click here to download:
    Option Strategies.xlsm (1.83 MB)

     

    Now I don't know nearly enough about options, but I find them fascinating.  However, I've found that taking the time to play around and determine a good options strategy is a gigantic pain.  So I decided to create a spreadsheet to help me out with that.  Often times you will look at a range of options and wonder what the best strategy for combining options in such a way that they provide low downside and adequate returns is.  This spreadsheet is designed to help with that.

    The spreadsheet works in a simple fashion.  You enter your inputs into cells B2 to B7.  After inputting the relevant information assuming you have @Risk copy cell B8 into A31 and paste down.  If you do not have @Risk copy cell G2 into cell A31 and paste down.  Once you have generated 10,000 random stock paths copy and paste them as values.  The next step is to get the contract prices from Yahoo finance.  Simple select the stock you are looking at and copy the Call data and then paste into cell O1 as text.  At that point you can simply enter the different strike prices into cells B16 to H16.  The bid and ask prices for the contracts should automatically be pulled into the spreadsheet.

    Now it's time to setup solver.  The inputs for the spreadsheet are in blue text and are fairly self-explanatory.  After entering your desired parameters solver is set up to maximize the minimum return.  IE it’s going to try to keep us from losing money while providing a return.  Once solver has finished running you can check out the payoff histograms, path, and percentiles on the related tabs.  Solver is adjusting the cells in B12 through H12.  Negative values indicate purchasing options while positive values indicate selling options.

    Currently the spreadsheet only works with calls but it would be easy to extend to puts.  Hopefully people find this useful and if you find any logic errors please let me know!
    • Tweet

  • Discriminant Analysis with Stocks

    • 12 Oct 2011
    • 0 Responses
    •  views
    • Discriminant_Analysis Excel Securities
    • Edit
    • Delete
    • Tags
    • Autopost

    Click here to download:
    Finviz 10-12-2011 Discriminant Analysis.xlsm (181 KB)


    In its most basic form discriminant analysis assigns weights to different factors and creates a rule based upon those weights and factors to classify a security (anything really) into two categories.  In our case it will be outperform and not outperform. I want to emphasize that this is not a hard and fast rule, but may help you narrow down the vast number of stocks into a manageable number of stocks.

    The first step is to determine which factors that you think are important in determining whether a stock will outperform or not outperform. It could be P/B, P/E, PEG, cash flow measures, or you could throw everything and the kitchen sink at the model and let it determine which ratios are most important. Once you have determined which ratios you want to use you need the historic values of those ratios and the current market returns of those securities.  You can then load these ratios into an excel spreadsheet.  The Finviz screener is a good place to get current data.

    Once you have loaded the data into excel it is a simple process of using the evolutionary solver to create a model which will classify securities between outperform and not outperform.  You will be creating a factor model that will multiply solver values by the ratios you are using.  Solver will compare them with the rule and try to minimize the errors associated with the model.  For values greater than the rule the stocks are expected to outperform and for values less than the rule stocks are expected to not outperform.  I've attached a spreadsheet that looks at the technology sector it got ~75% of it's classifications for out-performance correct but the data is contaminated. However, if you use uncontaminated data from a source such as bloomberg or download a monthly/weekly file from Finviz it should facilitate this process so that you can perform it on a regular basis and apply the rules to stocks in the market today.

    There are many factors unaccounted for by this model but it should generate some ideas and some companies to focus on.  Additionally, this link provides a paper which focused on a few algorithms for selecting securities, discriminant analysis being one of them.

    http://www.springerlink.com/content/...3/fulltext.pdf

    If you have any questions fire away, I think my next post will be about bootstrapping beta and model drivers to create cash flow models that have a range of outputs to better evaluate securities using discounted cash flows.

    • Tweet

  • More Fun with Gephi & Poker

    • 31 Jul 2011
    • 2 Responses
    •  views
    • 2P2 Gephi Poker
    • Edit
    • Delete
    • Tags
    • Autopost

    5topology4diff

     

    Here is an example where I calculated the difference in edge weight between 60-80th percentile players vs 80th-99th percentile players. Larger nodes & edges = higher absolute difference in play style. I would have to check the sign on these calculations but what we can most probably see is that the 60-80th percentile players just aren't playing aggressively enough and that is impacting their w$wsf and w$wsd, and they are folding way too often to flop cbets. Could run this against positional data to see if they are playing out of position.

    Edit: Based on Clement's suggestion of using force atlas 2 I got a much tighter looking graph.
    5topology4diff2
    • Tweet

  • Poker Style Topology

    • 30 Jul 2011
    • 0 Responses
    •  views
    • Gephi Poker
    • Edit
    • Delete
    • Tags
    • Autopost
    I got turned on to an interesting piece of software called Gephi that is used for data exploration and network analysis, but I thought it might be fun to try and apply it to poker.  Edge width is associated with connectedness between differing attributes and win amount as well as edge color.  The closer to red the more associated the statistic is with a high win percentage.  Node size is also associated with connectedness.  Each statistic is labeled 1 through 5 with 1 being the bottom 20 percentile and increasing in 20 percentile increments.  What is interesting about winning play styles is that we can see that there are many different ways to win, but in the end of the day aggression is the name of the game.  Granted all of this information (approximately 2 million hands) is from poker at relatively low limits so if anyone has some higher limit data (1000NL) they want to throw my way I would love to play with it.  Now I just need to figure out how to move the labels to the bottom of the nodes.

    Edit: Posterous is changing the size of the image and that's why it isn't clear.

     

    5topology4

    5topology2
    5topology3

    • Tweet
  • « Previous 1 2 3 Next »

  • About

    MBA Student. Aspiring Schemer. Interested in all things tech, analytics and finance.

    1775 Views

  • Archive

    • 2012 (8)
      • January (8)
    • 2011 (44)
      • October (5)
      • July (4)
      • June (2)
      • May (17)
      • April (16)

    Get Updates

    Subscribe via RSS
    TwitterFacebook