Using Array Names in Excel
How many times has this happened to you? You’re working with data in Excel. You create a detailed, nested formula that is meant capture an intersection of several arrays. After copying the formula down the column, your results don’t match your expected output. Cursing to yourself about faulty and missing data, you set off to find the offending row. After investing 5-10 fruitless minutes, you look back to your formula and shake your head. You forgot to create a string so the formula is pulling data from down the column.
If you’re anything like me, this happens more frequently than you would like to admit.
Naming each array is a surefire way to avoid this problem and streamline your work. It also allows other people to follow your logic when they look at your sheet. In this post, we’ll walk through using an example that incorporates array names.
I don’t normally discuss politics in polite society but I had an interesting conversation with someone about the recent NJ Governor’s election. I asked if he voted and he said “no, I don’t follow it, I don’t understand it and I don’t especially care.” He added, “Since that’s the case, I don’t think it’s right for me to vote. I don’t know any of the candidates, so I might vote for someone that I don’t want.”
We went our own ways but the conversation didn’t sit well with me. Here’s why.
That’s exactly what the powers that be – large corporations and their bought politicians – are counting on people to do. The less people pay attention to what’s happening, the easier it will be for those groups to do whatever they want. Trust me, that’s NEVER in your best interest.
So, why does it matter that people pay a little bit of attention and vote?
Recently, I posted that I was working on learning the Ruby language. I have a very basic background in programming but I want to learn a current language so that I can (hopefully) apply it in everyday situations. With that in mind, I signed up for a course on Udemy and let it rip.
I’m just about finished with the course (and I’ll have a shiny, new certificate to prove it) so now it’s time to start using my skills. Originally, I decided that I wanted to create a web scraping program to collect data from a race result so that I could analyze split times. I thought that this would be a great way to improve my skills and collect the data that I wanted to see in the process.
I’ve changed my mind.
Due to a professional opportunity, I am tabling the web-scraper in favor of a fuzzy logic model. Taking an existing problem and turning it into a program seems like a better use of the tools that I learned in the course. The web-scraper is good, and I’ll still do it, but this is better (read I think I can execute it in smaller chunks and have an easier time of it.)
Why an academic paper? I’m working on building a forecasting model and so I am learning about using fuzzy math to build the forecasting logic. The paper in question is one of the first steps in the modeling process. Before I can create a state-of-the-art model, I need to understand what state-of-the-art means. The logic, which consists of a variety of if/then branches, seems to lend itself to a program. That drove my decision.
This might be a bit more complex than prior posts. You’ve been warned.
Anyone who has been paying attention over the last few years (decades) knows that programming is something that everyone should
know how to do have a basic understanding about, regardless of profession. In addition to being able to make better use of everyday apps like Excel or Outlook, being even a tiny bit knowledgable about programming will help you navigate the web better and avoid potential problems and spying. Moreover, software is replacing humans everywhere, including “white collar” jobs that used to require a professional degree, so knowing something about it can’t hurt.
With that background, I decided it’s time to learn a current language. I’ve dabbled in Java in the past and I have a pretty good knowledge of CSS and HTML, but I want to add a more modern variant. Of course, R is a scripting language too, but I want to have something a bit broader in my arsenal. Ruby is my weapon of choice and Udemy is my vehicle. I plan to make a series of posts as I find ways to put this new effort to the test.
As a first task (actually, second – I’ve already built the basics of a portfolio rebalance engine using arrays), I am going to work on designing a web-scraper to collect ordered data. I recently had the pleasure of watching a number of friends and family participate in a triathlon. There were 1054 participants and the finish times and splits are spread across 11 pages. It would be easy enough to copy/paste all the data into Excel, but this limited data set seems like the perfect opportunity to learn the mechanics of automating the process of collecting data by parsing HTML.
Stay tuned for the the steps and eventually, results. Once I’ve collected all of the data, I will slice it up and see what I can learn about triathlon times.
Anyone with a basic knowledge of Excel can display data visually using a chart. Whether it’s a pie chart that shows each category’s percent of the whole or a line chart that shows the volatility of a single variable over time, Excel provides passable charting out of the box.
What if you want to show data dynamically, though? That task isn’t so easy and requires a more intimate understanding of the program and some of its less well known features like range naming.
What is a dynamic chart?
It’s been said that in the long run, the markets are a weighing machine but that in the short run they’re a voting machine.
Successful investing in an exercise in overcoming human psychological deficits. There is a whole field devoted to exactly that, called behavioral finance/economics. It explores why we make the decisions we make and attempts to provide methods for improving the decision making process.
One of the reasons why math/statistics are so important in finance is that they provide black and white signposts on what has happened in the past. We all know how important it is to learn from history. Wise investors use these guideposts to reduce the amount of noise – biases – in any decision. It’s a useful tool and it’s the reason that most professional investors appear cynical and detached. Once you understand biased decision making, you see it everywhere and you know how to try and fix it.
Sadly, behavioral finance fails to address the most difficult trait to master. How to do nothing.
Do something, anything.
If someone asked you what the chance of experiencing another Sandy over the next 10 years is, what would your answer be? How about this year? Or the next 50? If you’re feeling inclined, write down your answers.
I got to thinking about this recently because FEMA updated the flood maps for coastal NJ. I wanted to understand what they are telling me when they say that last year’s flood was a 100 year event. More importantly, I needed to figure out the chance of having a similar flood is over some discrete time frame in the future. I like to quantify my risks.
Some definitions are required before we start. A 100 year flood zone means that there is a 1.0% chance of a flood of similar or larger magnitude in any given year. It does not mean that a flood like the one Sandy caused occurs once every 100 years. Second, we are going to assume that this is a discrete event. Either we have a flood as bad as Sandy or we don’t. The math is complicated enough without trying to account for intensity. Third, we are not going to add the moon cycle or tides (remember that Sandy hit during a full moon at high tide; the water was already uncharacteristically high). We will assume that these factors are implied since this is a flood probability and not a storm probability.
So, if there is a 1% chance of a flood like Sandy caused in any given year, should we worry about having another one soon?