MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Accuracy formula

Posted by Tan Le on September 14, 2001 12:30 AM

Hi all,

I have a non-excel question but I think you guys, the floating point guru, can lead me to the better answer.

Given: a stock price is closed at 10 USD. I predict that in 1 week (5 trading days) that it will be 13 USD. My friend predicts higher than my guess. He thinks that it will go to 15 USD. But after one week, the price was closed at 14 USD.

Question: If you let either one of us know who is more accurate in predicting the future price in 1 week, who would that be? Given that both of us hit within plus and minus 1 unit of 14 USD. But my guess is lower and his is higher. My guess is the accuracy formula is the ratio of guess over actual? What do you think? How about the momentum of the guess, does that affect the accuracy percentage? If I guessed the price to be 14 USD and it closed at 14 after 1 week, then the ratio is 1. That's 100%. Had I guessed it to be 12 and closed at 14, my accuracy is 85%? That does not sound right. Maybe I am wrong. Also, for overguessing versus underguessing, which one is usually more favored in real world?

I sincerely appreciate your input as many as possible.

Tan Le

Posted by . on September 14, 2001 5:10 AM

Do you have any more stock tips like that?

Posted by Tan Le on September 14, 2001 7:12 AM

Re: Do you have any more stock tips like that?

To whom it may concern:

I am wanting to know if there is such an equation that exists close to it in terms of Excel functionality. Hopefully, the group here knows something. I don't mean to advertise or spam the awesome Mr.Excel's message board.

I am working to integrate that into where it finds other investors who has similar porfolio. The gamma phase of completion will take place in October. Please check it out and let me know what you think.

Posted by Eric on September 14, 2001 10:24 AM

I don't know microsoft stock from chicken broth but...

My take on it is that you are not predicting that the stock will be 15$ a share in 4 days (or in whatever timeframe). Instead you are predicting the amount that the stock will change- in this example, 5$. So the accuracy figure should not include the initial stock price, since that is not what you are predicting. Your accuracy/error rating might make more sense using something like
"initial stock"=10
"predicted stock at time"=15
"actual stock at time"=14
then difference between initial and =change predicted(15-10=5)
and difference between initial and actual=actual change (14-10=4)
subtract predicted change from actual change(5-4=1) to get "prediction error". Note that in this scenario the prediction error is postive indicating that it overestimated the stock.
I don't know if it would be useful, but you could also devise a % error scheme by dividing the "prediction error" value (1) by the "actual change" (4) to get a percent error (25%).
As for tie breaking when one prognosticator predicts high and one guesses low, I think you'd need to take into account 3 factors.
1) was the stock estimated to increase or decrease?
2) was the predicted change above or below the actual change
3) and probably most importantly, was the person for whom you are predicting a buyer or seller in the particular transaction- remember buy low sell hi?
Just some food for thought- hope it stimulates fruitful debate within your group!