Automatic MOS Calculator Watchlist for Microsoft Excel --- *Temporarily Offline*

[athletics]

Originally Posted by maxfisher05

This fixed the "Invalid Web Query" bug for me... let me know if this works for anyone else.

go to:

C:\Documents and Settings\Max\Local Settings\Temporary Internet Files

Make sure you have hidden files and folders set to visible, and obviously replace "Max" with "Your user name"

Select all. Then hit delete. If it asks you if you want to delete your cookies you can say no.

This worked for me, Im using XP SP2 and Office 07.

I deleted my temporary internet files and I still get the Invalid Web Query. I can usually get these things figured out, but this one has me stumped.

I am on XP SP2 but not on Office 07, could that be an issue?

[Hobbes]

Originally Posted by athletics

I deleted my temporary internet files and I still get the Invalid Web Query. I can usually get these things figured out, but this one has me stumped.

I am on XP SP2 but not on Office 07, could that be an issue?

I have built a similar excel speadsheet that pulls data from MSN and Yahoo Finance. When I have seen the "Invalid Web Query" message it is almost always the same problem. It seems as though Excel has a defect in the query engine (possibly due to threading) where if too many queries are being generated then they collide and return the invalid message. It happens to me on the 5th web query. If I force 4 web queries to refresh when I update a cell then there is no problem...if I add a 5th it throws the error. Rather than spending the time to figure out how to get around it I just have my web queries fire off of different fields (i.e. I enter in the ticker symbol 3 times instead of once...because I have 12 web queries that run and I can only fire 4 at a time). I can describe for you how you can change the field that your web queries fire off of but I'm not sure if you want to dig into the functionality of your spreadsheet...

[petekoch]

Hobbes, you might want to edit your queries and verify that background queries are not allowed. I wrote a few sheets with multiple queries and do not have similar problems. Could slow things down, though.

As for the problem with ROIC, I reported a bug (in the appropriate thread) and provided a circumvention.

One thing I never do is SAVE the MOS_CALC worksheet when I close; could be saving a corrupted file. I also keep a virgin copy in another folder in case I mess up.

[docdestructo62]

Originally Posted by maxfisher05

This fixed the "Invalid Web Query" bug for me... let me know if this works for anyone else.

go to:

C:\Documents and Settings\Max\Local Settings\Temporary Internet Files

Make sure you have hidden files and folders set to visible, and obviously replace "Max" with "Your user name"

Select all. Then hit delete. If it asks you if you want to delete your cookies you can say no.

This worked for me, Im using XP SP2 and Office 07.

Brilliant! I was still having trouble off and on. Recently, the cell for current EPS wasn't refreshing. Now it all works again!

[Hobbes]

Originally Posted by petekoch

Hobbes, you might want to edit your queries and verify that background queries are not allowed. I wrote a few sheets with multiple queries and do not have similar problems. Could slow things down, though.

As for the problem with ROIC, I reported a bug (in the appropriate thread) and provided a circumvention.

One thing I never do is SAVE the MOS_CALC worksheet when I close; could be saving a corrupted file. I also keep a virgin copy in another folder in case I mess up.

Thanks, I'll try that out...

[Justin]

An update has been posted for the Auto MOS Calculator. Another member of the forum suggested a slight modification to the formula behind cell "E6" of the Main tab of the spreadsheet.

The formula in Cell E6 is =ROIC!B5*0.01

When '#VALUE' appears, an examination of the ROIC sheet shows additional (and unwanted) data in Column A, usually 'fyi'. I circumvented this problem by changing MAIN Cell E6 to:

=IF(ROIC!$A$5="Return On Capital",ROIC!$B$5*0.01,ROIC!$C$5*0.01)

Thanks to petekoch for this suggestion as it should help with some of the #VALUE errors some have been running into.

You can download the latest version of the spreadsheet in the original post of this thread.

[SeanieT]

Originally Posted by Justin

An update has been posted for the Auto MOS Calculator. Another member of the forum suggested a slight modification to the formula behind cell "E6" of the Main tab of the spreadsheet.



[/b]Thanks to petekoch for this suggestion as it should help with some of the #VALUE errors some have been running into.

You can download the latest version of the spreadsheet in the original post of this thread.

The same thing happens to the Cash Flow Sheet. Any recommendations on that as far as a formula. Thank you for the fix. I wouldnot have figure that one out.

[Hobbes]

Originally Posted by SeanieT

The same thing happens to the Cash Flow Sheet. Any recommendations on that as far as a formula. Thank you for the fix. I wouldnot have figure that one out.

The cash flow statement from MSN is a very dynamic page. There are 2 issues that I had. First, some companies have more categories than others...so the rows you are looking for move up and down. Second, if a company has less that 5 years of data the columns shift around.

I get the row numbers I am looking for by matching to the category names:
=MATCH("Cash from Operating Activities",A1:A100,0)
=MATCH("Purchase of Fixed Assets",A1:A100,0)
=MATCH("Total Cash Dividends Paid",A1:A100,0)

I get the column number by matching to the year:
=IF(ISERROR(MATCH(2006,B3:F3,0)),0,MATCH(2006,B3:F 3,0))
=IF(ISERROR(MATCH(2005,B3:F3,0)),0,MATCH(2005,B3:F 3,0))
...etc.

Then you can get the values using these indexes...for instance here would be the 5 years of operating cash. In this case cell I4 is the row number from the first example and I8,I9,I10, and I11 are the columns for 2005, 2004, 2003, and 2002 respectively...2006 is always in column B:
=INDEX(B$1:B$57,$I$4)
=IF(I8=2,INDEX(C$1:C$57,$I$4),IF(I8=3,INDEX(D$1$ 57,$I$4),IF(I8=4,INDEX(E$1:E$57,$I$4),IF(I8=5,INDE X(F$1:F$57,$I$4),"N/A"))))
=IF(I9=3,INDEX(D$1$57,$I$4),IF(I9=4,INDEX(E$1:E$ 57,$I$4),IF(I9=5,INDEX(F$1:F$57,$I$4),"N/A")))
=IF(I10=4,INDEX(E$1:E$57,$I$4),IF(I10=5,INDEX(F$1: F$57,$I$4),"N/A"))
=IF(I11=5,INDEX(F$1:F$57,$I$4),"N/A")

[SeanieT]

Originally Posted by Hobbes

The cash flow statement from MSN is a very dynamic page. There are 2 issues that I had. First, some companies have more categories than others...so the rows you are looking for move up and down. Second, if a company has less that 5 years of data the columns shift around.

I get the row numbers I am looking for by matching to the category names:
=MATCH("Cash from Operating Activities",A1:A100,0)
=MATCH("Purchase of Fixed Assets",A1:A100,0)
=MATCH("Total Cash Dividends Paid",A1:A100,0)

I get the column number by matching to the year:
=IF(ISERROR(MATCH(2006,B3:F3,0)),0,MATCH(2006,B3:F 3,0))
=IF(ISERROR(MATCH(2005,B3:F3,0)),0,MATCH(2005,B3:F 3,0))
...etc.

Then you can get the values using these indexes...for instance here would be the 5 years of operating cash. In this case cell I4 is the row number from the first example and I8,I9,I10, and I11 are the columns for 2005, 2004, 2003, and 2002 respectively...2006 is always in column B:
=INDEX(B$1:B$57,$I$4)
=IF(I8=2,INDEX(C$1:C$57,$I$4),IF(I8=3,INDEX(D$1$ 57,$I$4),IF(I8=4,INDEX(E$1:E$57,$I$4),IF(I8=5,INDE X(F$1:F$57,$I$4),"N/A"))))
=IF(I9=3,INDEX(D$1$57,$I$4),IF(I9=4,INDEX(E$1:E$ 57,$I$4),IF(I9=5,INDEX(F$1:F$57,$I$4),"N/A")))
=IF(I10=4,INDEX(E$1:E$57,$I$4),IF(I10=5,INDEX(F$1: F$57,$I$4),"N/A"))
=IF(I11=5,INDEX(F$1:F$57,$I$4),"N/A")

Wow, Thanks for this fix. I hope that I can incorporate this into my spreadsheet. I am tired of having to go back and forth readjusting my Cash Flow formulas.

[piazzi]

Hi Justin,

MSN gives Canadian numbers with an annoying CAD at the end. Other than that spreadsheet is just fine for Canadian Companies.

I do not expect you to fix that for me (maybe the only Canuck around here)

But if you tell me where in the code is the best place to go and strip the CAD from prices, I'll do it myself

Thanks,

piazzi

Where is the best place

Originally Posted by Justin

The latest edition of our Automatic Margin of Safety Calculator can be downloaded from this location

http://www.roicommunity.com/uploaded/mos_calc104.xls

Thanks,

Justin