Generating Sitemap Links with Excel

We have become good friends with the Ysais family of Kyle, Texas.  They visited us in Colorado last summer and we all had a great time.  Corrina stays in touch with Marie almost every day, our daughter Skypes and plays Minecraft with the two boys, and Michael taunts me with mouthwatering photos of his smoked brisket.

Marie runs Ysais SEO, which helps businesses optimize their search engine results.  She asked me to help with an Excel workbook to create Google sitemap links.  I took the file she sent and worked it into a tool that can create several links for each specified website.

I thought I would share the workbook because it makes a good example of how to use Excel’s table feature and its HYPERLINK function.  With Marie’s consent, here is the workbook. Click on the image to open the file.

sitemap generator

There are four sheets, each with its own table (note that these are not “data tables”).  The first three tables provide the building blocks for the links: the Google sitemap text (the “prefix”), the domain names, and part specifiers (the “suffix”).  On “SiteMaps”, the last tab, a table generates hyperlinks for all possible combinations of the three text blocks.

To use the workbook, make sure that the prefix text meets your needs.  As with all inputs in this workbook, the prefix text is colored blue.  You can insert rows above if you need more than one prefix. Next, enter the domain names.  There is room for 500 domain names.  Blanks are fine, and you can always insert more rows if you need more.  The final input step is to review the suffix text.  You can insert more suffix items if you need more.

Now go to the sitemap generator on the last sheet.  Click on the “Good?” filter and select “TRUE” only.  This will hide the blank links.  Be sure that the bottom of the “Last?” column shows a “TRUE” value.  This insures that the last possible combination of your text blocks has been reflected in the sitemap list.  If not, you will need to insert more rows into this table.

Marie reports that the workbook also made it easy for her to create Bing sitemaps.

Note: a good way to insert rows into any of these tables is to click on the cell labelled “Count” at the bottom of the first column.  Insert the number of needed rows and copy down any formulas from above as needed.

Please let me know if you have any questions.

Advertisements
Posted in Spreadsheet Tips | Tagged , , , , | Leave a comment

Spaghetti Puzzle #1

About a month ago, math educator Marilyn Burns tweeted this puzzle:

 

Spoiler Alert: Stop here if you want to solve the puzzle yourself!  Answers (some correct and some not) will be given below.

This innocent-seeming puzzle stirred several math teachers to exchange ideas, diagrams and animations.  Some of them changed their minds about it as they worked through the problem.  For example, Simon Gregg first thought that the answer is 1/4:

Upon further reflection, he changed his answer to 1/3:

There were some who used calculus to find a probability of 0.386.  Gregg came to suspect they were right:

 

My Attempts

I approached the problem in a few ways myself.  I thought about the sample space as sketched by Kaleb Allinson:

Assuming the spaghetti stick is one unit long, the horizontal axis represents the length of the shorter piece after the first break.  Let’s call this A.  It must range between 0 and 1/2.  The longer piece is then itself broken into a left piece with length B and a right piece with length C.  I interpret the vertical axis in Allinson’s chart as the length B.  It could range between just above zero to nearly one if A is very small, and it could range between between zero and around 1/2 if A itself is close to 1/2.  The length C of the third side is not displayed in this diagram, but we can find it for any point (A,B) by using C = 1 - A - B.

The sample space of possible (A,B) points combines the three right triangles in Allinson’s diagram.  The shaded area in the middle represents the combinations of A and B that can lead to a spaghetti triangle.  These combinations work because of the triangle inequalities, which require that no side be longer than the sum of the other two sides.  Otherwise, the two shorter sides might just dangle pathetically off the long side like the arms of a T-Rex.

Allinson saw that one-third of the sample space corresponded to valid triangles, and so concluded that the probability of forming a triangle is 1/3.  However, all parts of the diagram are not equally likely.  All permitted values of A are equally likely, but the distribution of B depends on A.  When A is very small, there is about a 50% chance that B is less than 1/2.  In contrast, when A is near 1/2, there is almost a 100% change that B is less than 1/2.  The probability density in the diagram is not uniform; it increases from left to right.

In my mind, I pictured the probability density as a slanted roof above the diagram (in a third dimension).  By some mental calculation I concluded that the probability of landing in the shaded area (what we might call the “triangle of triangles”) was 1/2.

To check my result, I made a spreadsheet to simulate many random spaghetti snaps.  For each simulation, I sampled values for two random variables X and Y, each uniformly distributed from 0 to 1.  I used X to set A, the length of the shorter piece after the first break.  We can let A equal the lesser of X and 1-X.  We then find the next piece with B=Y\times (1-A).  As mentioned above, the third piece C=1-A-B.   I set up the model to run 10,000 simulations and flag which ones could form triangles.

Spaghetti sides

This is the result of one run with the A and b values plotted for each simulation.  The blue dots indicate valid triangles.  It looks like one-third of the points are good for triangles.  However,  the points are more crowded on the right than the left, and there are 3,823 blue dots out of 10,000 total.  The blue triangle is denser than the orange areas and gets more than its share of the simulations.

spaghetti random 2

Here is the same run plotted against the random variables X and Y.  The blue dots take up about 38% of the area of this evenly distributed chart.

I ran the spreadsheet several times and each time about 38% or 39% of the simulations allowed triangles.  I had to doubt my answer of 1/2.  I realized that the probability surface was not simply flat incline but something with a curve to it.

I communicated by Twitter and email with Mike Andrejkovics, a math teacher and math rapper(!) .  He thought at first that the answer was 1/4 and he created a cool video to make his case.  Mike then realized that his solution applied to a slightly different problem (see Variations below).

Like some of the other math teachers on Twitter, Mike used calculus to find the answer of .386 for the Marilyn Burns version of the problem.  I decided to work through the calculus for myself.  Mike had gotten me thinking some more about the probability density.  None of the calculus solutions I had seen had provided an explicit formula for the probability density, but I thought that such a formula would clarify the problem for me.

Let f(x,y) be our probability density function (“pdf”) over the sample space in Kaleb Allinson’s diagram.  This space ranges from 0 to 1/2 for the x coordinate and from 0 to 1-x for the y coordinate.  We use x to represent the length of the shorter piece after the first break.  After the longer piece is broken into two pieces, the new piece on the left has length y.  The total probability over this space should equal one.  In calculus notation, this means

\iint_{S}f(x,y)dydx=1  or

\int_{0}^{1/2}\int_{0}^{1-x}f(x,y)dydx=1.

The first statement integrates f over the sample space S.  The second statement is equivalent.  Working from the inside out, we begin by integrating f as y ranges from 0 to 1-x.  This integral is basically just a function of x, so we can write g(x)=\int_{0}^{1-x}f(x,y)dy.  Now our double integral becomes the single integral

\int_{0}^{1/2}g(x)dx=1.

We want g(x) to be constant, because we assume that  x is uniformly distributed from 0 to 1/2.  The probability density in the sample space for each value of x should be the same.  Now if g(x) is constant, then it must equal 2 so that \int_{0}^{1/2}g(x)=1.  Now we work back to the function f.   We need

\int_{0}^{1-x}f(x,y)dy=g(x)=2,

which works with the pdf f(x,y)=\dfrac{2}{1-x}.  Note that f is  a function of x but not of y.  It grows along a curve from left to right.

Let’s look at this probability density function over the sample space.  The volume enclosed by the figure is exactly one cubic unit, as required.

sample space

I like this shape.  Every side could be made from a flat or rolled sheet of paper.  Maybe architects should study probability density functions for inspiration!

Now let’s complete the solution to the spaghetti problem.  We want to know the probability of landing in the shaded “triangle of triangles.”  Call this region T.  Then the probability of making a triangle is

\iint_{T}f(x,y)dydx

=\int_{0}^{1/2}\int_{1/2-x}^{1/2}f(x,y)dydx

=\int_{0}^{1/2}\int_{1/2-x}^{1/2}\dfrac{2}{1-x}dydx

=\int_{0}^{1/2}\dfrac{2x}{1-x}dx

=2ln(2)-1\approx 0.386

where ln represents the natural logarithm.  I hope you don’t mind that I skipped over some of the integration steps at the end.

Let’s see what the solution looks like.  Here is the probability density with the region for valid triangles colored in red.  The volume of the red portion is 0.386.

triangle solution

 

Variations

The first version of the puzzle that Mike Andrejkovics solved appears as the “Walking Stick Puzzle” (#120) in The Puzzle Universe by Ivan Moscovich.  This book was a great Christmas present from my wife.  In the Walking Stick problem, the two breaks are made independently.  It is not required that the second break occur along the longer piece after the first break.

I told my daughter (with whom I blog at Zeno’s Meatball) about the spaghetti puzzle.  She interpreted the problem in physical terms.  How do spaghetti strands really break?  Aren’t they more likely to break near the middle than near the ends?  Perhaps we grownups have been too simplistic with our uniform distributions.

Stayed tuned for a new variation on the puzzle that I will present in an upcoming post.  I hope you enjoy it.

Copyright 2016.  All Rights Reserved.

Posted in Fun, Puzzles | Tagged , , , , | 6 Comments

A Mathematician’s New Year’s Resolutions

Speaking of the new year, here are some funny resolutions from the “Math with Bad Drawings” blog. I like the one about completing chores in finite time. I bet Zeno never finished cleaning his room!

Math with Bad Drawings

Mathematician New Years 0Mathematician New Years 1

View original post

Posted in Uncategorized | Leave a comment

Happy 5 2 0 1!

Last year,  in Happy 0 0 1 0 0 1 0 0 0 0 1!, I discussed an interesting way to represent numbers based on their prime factorization.  For example, 63 is represented by 0 2 0 1 because 63 = 20 x 32 x 50 x 71.

In the prime power representation, 2015 was 0 0 1 0 0 1 0 0 0 0 1. However, with 2016 = 25 x 32 x 50 x 71, this year can be represented by the more concise  5 2 0 1.

I doubt you can express next year’s greeting in a single breath.  Because 2017 is the 306th prime, it will take the form “Happy 0 0 0  …  0 0 0 1!“, where the one is preceded by 305 zeros.

 

Posted in Math | Tagged , , | Leave a comment

The Money We Live With

TheMoneyWeLiveWith_MatthewJHergott_300x480

My friend Matt Hergott has just published The Money We Live With: Understanding the World Through the Prism of Modern Money, an Amazon eBook.

From the Introduction:

This book is about contemporary money, how it is used by policymakers to try to achieve specific outcomes, and what it all means for ordinary people.  With a better understanding of modern money, people will be able to interpret more clearly the flow of history, understand better why those in positions of authority make the historic decisions they do, and better assess how those choices can affect the lives of ordinary people.

Matt is an astute analyst of investments and economics.  He gave a presentation about hedge fund replication to the Denver Chapter of the Global Association of Risk Professionals  (“GARP”) in 2013.

I look forward to reading his book, and I will share my comments in a future post.

Posted in Economics | Tagged , , , | Leave a comment

Fast Formulas #6: Finding the Time

Florence Cathedral 24-Hour Clock. Credit: Wikimedia Commons

Florence Cathedral 24-Hour Clock: Wikimedia Commons.

 

The Fast Formulas on this blog provide shortcut calculations for financial instruments such as mortgages and bonds. Most formulas involve the symbol n to represent the number of periods (e.g. years or months) until the end of the instrument. Usually n is already known: for example, you might know how long the mortgage is, but you need to find the monthly payment. Sometimes, though, you don’t know the timespan, and you need to figure it out from other information.

Time to Required Future Value

Let’s start with a simple example. Suppose you are able to invest some of your money at 5% per year, compounded annually. You wish to grow your $50,000 investment to $100,000.  How long will that take? In other words, what is n in the time value of money[1] equation,

FV=(1+r)^{n}PV,                                                                                                      (Formula 6.1)

where PV , the present value, is $50,000; FV , the future value, is $100,000; r, the interest rate, is 5%; and n is the unknown number of compounding periods?

To find n, divide both sides by PV and then take the natural logarithm[2] of both sides:

\ln \left( \dfrac{FV}{PV}\right)=\ln((1+r)^{n}).

Because logarithms have the nice property that \ln (a^{n})= n \ln (a), we can move the exponent n to the outside of the logarithm:

\ln \left( \dfrac{FV}{PV}\right)=n \ln(1+r),

leading to the Fast Formula for the number of periods in the time value of money equation:

n=\dfrac {\ln \left( \dfrac{FV}{PV}\right) }{\ln (1+r)}.                                                                                                            (Formula 6.2)

In can use this formula to calculate the number of periods in our example:

n=\dfrac{\ln(2)}{\ln(1.05)}\approx\dfrac{0.6931}{0.0488}\approx14.2067.

So it will take a little more than fourteen years to double your money at 5%.[3]

You could also apply Formula 6.2 to problems with single-payment securities such as zero coupon bonds, T-Bills, or commercial paper. For example, suppose you know that a zero-coupon bond has been priced at 70% to yield 4%, but you don’t know the maturity. Apply Formula 6.1 with semiannual compounding (which is typical for bonds) to find that:

n=\dfrac {\ln\left( \dfrac {100}{70}\right)} {\ln \left( 1+\dfrac {0.04}{2} \right)}\approx\dfrac {0.3567}{0.0198}\approx18.0115.

So the bond will mature in about nine years (eighteen semiannual periods).

Time to Pay Off Mortgage

Let’s apply the logarithm method to another problem. Suppose you have just taken out a $100,000 30-year mortgage at 4.2%. As we know from Formula 1.3, your monthly payment is given by

L=rPD_{n}.                                                                                                                   (Formula 1.3)

where

L is the monthly payment;
r is the monthly interest rate (0.35% in our case);
P is the principal amount;
n is the term of the mortgage, in months; and
D_{n}=\dfrac {1} {1-d^{n}} , where d=\dfrac {1} {1+r} .

Applying Formula 1.3 gives us the monthly payment of $489.02.

Now suppose you wanted to accelerate your mortgage by paying $1,000 a month. How much sooner than thirty years would the mortgage expire?

To answer this question, let’s work with Formula 1.3 to extract and isolate n from Formula 1.3.  Begin by replacing D_{n} with its definition:

L=\dfrac {rP}{1-d^{n}}.

After a few steps, you can isolate d^{n}:

d^{n}=1-\dfrac {r}{L}P.

Now take logarithms of both sides:

\ln (d^{n})=\ln(1-\dfrac {r}{L}P,

and, as before, move the exponent to the outside of the logarithm:

n\ln(d)=\ln(1-\dfrac {r}{L}P,

and then solve for n:

n=\dfrac {\ln\left( 1-\dfrac {r}{L}P\right)}{\ln(d)}.                                                  (Formula 6.3)

Since you decided to pay $1,000 a month, we change L to $1,000.  Now we find that

n=\dfrac{\ln\left(1-\dfrac{0.0035}{1,000}\times 100,000 \right)}{\ln\left( \dfrac {1}{1.0035} \right)}\approx\dfrac {-0.4308}{-0.0035}\approx123.2961.

The mortgage can be paid off in about ten years if you pay $1,000 a month for 123 months and a smaller payment in the 124th month.  An approximate doubling of the payment cuts the repayment time by about two-thirds.

In General

In the cases we examined, time to future value and time to mortgage payoff, the variable for the number of periods appeared exactly once in the original formula.  In such cases, you should always be able to isolate n and solve for it.  If n appears as an exponent, you will need to use logarithms to find the number of periods.  However, if n appears more than once in the formula, most likely there will not be a direct way to solve for it, but iteration should work.

 

[1]I always thought that it made more sense to say “money value of time” than “time value of money.”.

[2]You could use another logarithm but the natural logarithm is a better choice since it is essential to many financial calculations.

[3] You could have estimated this very easily by using the Rule of 72.

Copyright 2015.  All rights reserved.

Posted in Bonds, Fast Formulas, Math | Tagged , , , , | 2 Comments

Fourth Annual Municipal Finance Conference

I hope you can join us in Boston this summer at the Fourth Annual Municipal Finance Conference.  The conference will be hosted on August 7 by Brandeis International Business School and the Federal Reserve Bank of Boston.  The conference has evolved into an essential forum for academics and practitioners in municipal finance to engage each other and discuss the latest ideas and trends in the market.  The keynote speaker will be Kevyn Orr, who managed the bankruptcy process for the City of Detroit.

As a discussant on one of the panels, I will respond to a paper on the economic impact of ratings outlooks.  Space is limited, so please register before it is too late!

Posted in Bonds, Economics | Tagged , , , , | Leave a comment