Minggu, 11 Mei 2014

[smf_addin] Digest Number 3062[6 Attachments]

15 Messages

Digest #3062
1d
Re: FWallStreet DCF valuation spreadsheets by "Randy Harmelink" rharmelink
1f
Re: FWallStreet DCF valuation spreadsheets by "Randy Harmelink" rharmelink
3a
Total Fund Return from Morningstar by "andrei radulescu-banu" iubica2
3b
Re: Total Fund Return from Morningstar by "Randy Harmelink" rharmelink
3c
Re: Total Fund Return from Morningstar by "andrei radulescu-banu" iubica2

Messages

Sat May 10, 2014 4:26 am (PDT) . Posted by:

"Darryl Bonfessuto"

I have tried to use this spreadsheet and its not working, after clicking run just getting #name?, any thoughts whats wrong?

Thanks

Darryl

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Friday, May 09, 2014 4:52 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: FWallStreet DCF valuation spreadsheets

Hi Pete

I'm back here after ages, and lo and behold, you have already posted my favorite "F wall street" sp sheet

I thank my stars, start using it, encounter some errors on GF pages, change RCHGetTableCell formula as gurufocus seems to have changed some descriptions

I thank you, Randy and other gurus here, for this excellent community and your hard work

some questions
~~~~~~~~~~~~~~~

1. would my version be of any use ? : Is there some place on this group files section, I can upload my (version of) sp sheet on a " _purely_ for review" basis (not for all to download or depend on at this stage)
1.1. Temporarily it's uploaded to http://1drv.ms/1mKwAQm
1.2. mods / changes made to Gf sheet

2. why's Xl forgetting the data collected : I always thought that XL remembered the values that I picked up from the web. What do I mean, I mean IF I ran this sp sheet for tickers, WDC and XRX (default ones), and then without closing XL , if I came back and ran this sp sheet a second time for tickers WDC, XRX and MSFT, in the old scheme of things, Xl remembered the values it picked up for WDC, XRX and went searching the web only for the new tickers. However this time though it seems to be doing it all over again. what should I tweak in XL to improve it's memory ?
2.1. I'm using XL 2010, version 14.0.7106.... blah ..blah on a win 7 machine

3. Max download of data, infuriating website owners, increasing blood pressure !!: To avoid all that , I used to download critical info once in three months for my standard set of tickers, keep it some place, use V lookup for the next n runs, and update only current price . I seem to have lost that magic sp sheet to download data and throw it some place ....

==========
regards
Subu

--In smf_addin@yahoogroups.com, <dan-carroll@...> wrote :

I think I've fixed the GF problems. GuruFocus changed the description of a few terms & I've updated it.
I haven't done a line-by-line check, so caveat emptor. If you spot any issues, let me know and I'll fix and update it.

Sat May 10, 2014 4:27 am (PDT) . Posted by:

"Darryl Bonfessuto"

Here is a copy of the spreadsheet.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Friday, May 09, 2014 4:52 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: FWallStreet DCF valuation spreadsheets

Hi Pete

I'm back here after ages, and lo and behold, you have already posted my favorite "F wall street" sp sheet

I thank my stars, start using it, encounter some errors on GF pages, change RCHGetTableCell formula as gurufocus seems to have changed some descriptions

I thank you, Randy and other gurus here, for this excellent community and your hard work

some questions
~~~~~~~~~~~~~~~

1. would my version be of any use ? : Is there some place on this group files section, I can upload my (version of) sp sheet on a " _purely_ for review" basis (not for all to download or depend on at this stage)
1.1. Temporarily it's uploaded to http://1drv.ms/1mKwAQm
1.2. mods / changes made to Gf sheet

2. why's Xl forgetting the data collected : I always thought that XL remembered the values that I picked up from the web. What do I mean, I mean IF I ran this sp sheet for tickers, WDC and XRX (default ones), and then without closing XL , if I came back and ran this sp sheet a second time for tickers WDC, XRX and MSFT, in the old scheme of things, Xl remembered the values it picked up for WDC, XRX and went searching the web only for the new tickers. However this time though it seems to be doing it all over again. what should I tweak in XL to improve it's memory ?
2.1. I'm using XL 2010, version 14.0.7106.... blah ..blah on a win 7 machine

3. Max download of data, infuriating website owners, increasing blood pressure !!: To avoid all that , I used to download critical info once in three months for my standard set of tickers, keep it some place, use V lookup for the next n runs, and update only current price . I seem to have lost that magic sp sheet to download data and throw it some place ....

==========
regards
Subu

--In smf_addin@yahoogroups.com, <dan-carroll@...> wrote :

I think I've fixed the GF problems. GuruFocus changed the description of a few terms & I've updated it.
I haven't done a line-by-line check, so caveat emptor. If you spot any issues, let me know and I'll fix and update it.

Attachment(s) from Darryl Bonfessuto
1 of 1 File(s)

Sat May 10, 2014 7:20 am (PDT) . Posted by:

hamishthedenizen

Darryl, I uploaded a version of the FWallStreet spreadsheet with a fix for the new GuruFocus data format, FWallStreet_GF_Mod. Try that and let me know if you are still having problems

Sat May 10, 2014 8:26 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You probably just need to fix the unresolved links...

On Sat, May 10, 2014 at 4:26 AM, Darryl Bonfessuto dmb7111@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:

>
> I have tried to use this spreadsheet and its not working, after clicking
> run just getting #name?, any thoughts whats wrong?
>
>

Sat May 10, 2014 11:32 am (PDT) . Posted by:

"darryl bonfessuto"

Randy,
When I try to save the downloaded file thats when the #name? appears in
excel boxes. Do I not have the correct addins? Before I save all boxes
have data. any thoughts.
Thanks
Darryl

On 5/10/14 11:26 AM, Randy Harmelink rharmelink@gmail.com [smf_addin] wrote:
> You probably just need to fix the unresolved links...
>
> On Sat, May 10, 2014 at 4:26 AM, Darryl Bonfessuto dmb7111@gmail.com
> <mailto:dmb7111@gmail.com> [smf_addin] <smf_addin@yahoogroups.com
> <mailto:smf_addin@yahoogroups.com>> wrote:
>
>
> I have tried to use this spreadsheet and its not working, after
> clicking run just getting #name?, any thoughts whats wrong?
>
>
>
>

Sat May 10, 2014 1:00 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sounds like you opened it with its previous values, presumably with
unresolved links. Whenever you save anything, it may try to recalculate to
get current values, but can't with the unresolved links, so converts the
cells to #NAME?

Do your cells have a location prefix in front of the formulas?

On Sat, May 10, 2014 at 11:32 AM, darryl bonfessuto
dmb7111@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:

>
> When I try to save the downloaded file thats when the #name? appears in
> excel boxes. Do I not have the correct addins? Before I save all boxes have
> data. any thoughts.
>

Sun May 11, 2014 5:16 am (PDT) . Posted by:

"Darryl Bonfessuto"

Randy,

When I click enable or open the file an excel message "this workbook contains links that cannot be updated appears" Dan 4 pic, with edit the link Dan 5 pic appears. I have enclosed Dan 6 of D6 box

Any thoughts. Obviously Randy I am not a computer master. Would you know anyone I could hire or pay to look into this and assist via phone or direct PC connections?

Thanks Darryl

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Saturday, May 10, 2014 4:00 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: FWallStreet DCF valuation spreadsheets

Sounds like you opened it with its previous values, presumably with unresolved links. Whenever you save anything, it may try to recalculate to get current values, but can't with the unresolved links, so converts the cells to #NAME?

Do your cells have a location prefix in front of the formulas?

On Sat, May 10, 2014 at 11:32 AM, darryl bonfessuto dmb7111@gmail.com <mailto:dmb7111@gmail.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:

When I try to save the downloaded file thats when the #name? appears in excel boxes. Do I not have the correct addins? Before I save all boxes have data. any thoughts.

Attachment(s) from Darryl Bonfessuto
3 of 3 Photo(s)

Sun May 11, 2014 5:46 am (PDT) . Posted by:

"Darryl Bonfessuto"

Yes Randy

This is a pic of the program file Dan 9 &10

Any thoughts

Darryl

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Saturday, May 10, 2014 4:00 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: FWallStreet DCF valuation spreadsheets

Sounds like you opened it with its previous values, presumably with unresolved links. Whenever you save anything, it may try to recalculate to get current values, but can't with the unresolved links, so converts the cells to #NAME?

Do your cells have a location prefix in front of the formulas?

On Sat, May 10, 2014 at 11:32 AM, darryl bonfessuto dmb7111@gmail.com <mailto:dmb7111@gmail.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:

When I try to save the downloaded file thats when the #name? appears in excel boxes. Do I not have the correct addins? Before I save all boxes have data. any thoughts.

Attachment(s) from Darryl Bonfessuto
2 of 2 Photo(s)

Sun May 11, 2014 8:07 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

See the "Location Errors" item in the "Tips and FAQs" area.

You just have those unresolved links because EXCEL saves a hard-coded
location of the add-in whenever it saves a file. When you open up the file
and your add-in is in a different location, it can't resolve those errors.

All you need to do is a "Find and Replace All" of all those location
prefixes, changing them to nothing. Then it will use your add-in functions.

There is an smfFixLinks macro within the add-in to do it for you. Try
running that macro. I have it assigned to my Quick Access Toolbar.

On Sun, May 11, 2014 at 5:16 AM, Darryl Bonfessuto dmb7111@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:

>
> Randy,
>
> When I click enable or open the file an excel message "this workbook
> contains links that cannot be updated appears" Dan 4 pic, with edit the
> link Dan 5 pic appears. I have enclosed Dan 6 of D6 box
>
>
>
> Any thoughts. Obviously Randy I am not a computer master. Would you know
> anyone I could hire or pay to look into this and assist via phone or direct
> PC connections?
>
>
>
> Thanks Darryl
>

Sat May 10, 2014 4:17 pm (PDT) . Posted by:

dudescholar

FYI,

I've been working with:


http://portfolios.morningstar.com/fund/holdingsExport?exportType=details&t=VTI http://portfolios.morningstar.com/fund/holdingsExport?exportType=details&t=VTI



for the 9 main sector ETFs and several additional ones. I'm finding a lot of problems in the symbol data for each ETF. For example in the Gold Miners ETF GDX, Goldcorp should be GG but is listed as G (Genpact), Kinross Gold as K (Kellogg), Primero Mining as P (Pandora Media), Eldorado Gold as ELD (a WisdomTree ETF), etc.


I was using the morningstar data and adding columns of info from finviz and decided to compare a number data points, company name, sector, volume, country, and was coming up with lots of discrepancy that led me to the symbol problem. Had thought to fix the symbols using finviz company names to locate the right symbols but the company names can be very different for example:
"Coeur Mining Inc" (morningstar) verses "Coeur d'Alene Mines Corporation" (finviz)
"Buenaventura Mining Co Inc ADR" versus "Compania de Minas Buenaventura SA".


Back to the drawing board.


Steve





Sat May 10, 2014 4:52 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The problem is that the ETF is a based on an international index, and you
are looking up "G" as a United States company, but it's marked as a
Canadian company. So the symbol to use would be "XTSE:G". And Kinross Gold
would be "XTSE:K".

http://quote.morningstar.com/quote/quote.aspx?t=XTSE:G
http://quote.morningstar.com/quote/quote.aspx?t=XTSE:K
http://quotes.morningstar.com/stock/p/s?t=XTSE:P
http://quotes.morningstar.com/stock/p/s?t=XTSE:ELD

And you do have other countries:

Newcrest Mining, Australia =
http://quote.morningstar.com/quote/quote.aspx?t=XASX:NCM
Zijin Mining, China =
http://quote.morningstar.com/quote/quote.aspx?t=XHKG:02899
Centamin PLC, UK = http://quote.morningstar.com/quote/quote.aspx?t=XLON:CEY

Etc. Etc. It is rather convoluted though. I'm not happy with it. The
download file should have the full symbol, including the exchange prefix.
Or, the exchange should be a separate column in the file.

On Sat, May 10, 2014 at 4:17 PM, dudescholar3@basicmail.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I've been working with:
>
>
> http://portfolios.morningstar.com/fund/holdingsExport?exportType=details&t=VTI
>
> for the 9 main sector ETFs and several additional ones. I'm finding a lot
> of problems in the symbol data for each ETF. For example in the Gold
> Miners ETF GDX, Goldcorp should be GG but is listed as G (Genpact), Kinross
> Gold as K (Kellogg), Primero Mining as P (Pandora Media), Eldorado Gold as
> ELD (a WisdomTree ETF), etc.
>
> I was using the morningstar data and adding columns of info from finviz
> and decided to compare a number data points, company name, sector, volume,
> country, and was coming up with lots of discrepancy that led me to the
> symbol problem. Had thought to fix the symbols using finviz company names
> to locate the right symbols but the company names can be very different for
> example:
> "Coeur Mining Inc" (morningstar) verses "Coeur d'Alene Mines Corporation"
> (finviz)
> "Buenaventura Mining Co Inc ADR" versus "Compania de Minas Buenaventura
> SA".
>
> Back to the drawing board.
>
> Steve
>

Sun May 11, 2014 8:14 am (PDT) . Posted by:

"andrei radulescu-banu" iubica2

Randy, thanks for writing and maintaining a most excellent app!

Trying to balance a 401k account here, and I have a newbie question for the
group about Morningstar total returns. Ticker is FMXKX. Looking at this
chart, clicking "1-Month", the total return is $10,345.05:

http://quote.morningstar.com/fund/chart.aspx?t=FMXKX®ion=usa&culture=en-US

Looking at this URL, however, the 1-Month return is .37%:

http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=fmxkx

I'm off by an order of magnitude, and I don't understand why. Obviously,
the 2nd URL is something the SMF tool can parse, thereby my interest.
(Well, in fact the SMF is parsing the total return instead from
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=FMXKX, for
efficiency, but that gives the same value .37%).

Thanks,
Andrei

--
==================================
Andrei Radulescu-Banu
86 Cedar St, Lexington MA
617.216.8509 (m), 781.862.5854 (h)
lex-wiki.org, lex4lang.org,
andrei4schools.com, bitdribble.com
==================================

Sun May 11, 2014 8:32 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The reason for the difference is that the "1-month chart" starts on 4/11.
But a 1-month total return would actually start on 4/9.

From 4/9 to 5/9 is $16.13 to $16.19, or 0.372%

From 4/11 to 5/9 is $15.65 to $16.19, or 3.4505%

Those are the two different total return numbers you're looking at.

Manually adjust the starting date on the chart to 4/9 and you'll see a big
drop on those first 2 days.

On Sun, May 11, 2014 at 6:40 AM, andrei radulescu-banu
bitdribble@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:

>
> Randy, thanks for writing and maintaining a most excellent app!
>
> Trying to balance a 401k account here, and I have a newbie question for
> the group about Morningstar total returns. Ticker is FMXKX. Looking at this
> chart, clicking "1-Month", the total return is $10,345.05:
>
>
> http://quote.morningstar.com/fund/chart.aspx?t=FMXKX®ion=usa&culture=en-US
>
> Looking at this URL, however, the 1-Month return is .37%:
>
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=fmxkx
>
> I'm off by an order of magnitude, and I don't understand why. Obviously,
> the 2nd URL is something the SMF tool can parse, thereby my interest.
> (Well, in fact the SMF is parsing the total return instead from
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=FMXKX,
> for efficiency, but that gives the same value .37%).
>

Sun May 11, 2014 2:21 pm (PDT) . Posted by:

"andrei radulescu-banu" iubica2

Thank you, Randy.

-- Andrei

On Sun, May 11, 2014 at 11:32 AM, Randy Harmelink
rharmelink@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:

>
>
> The reason for the difference is that the "1-month chart" starts on 4/11.
> But a 1-month total return would actually start on 4/9.
>
> From 4/9 to 5/9 is $16.13 to $16.19, or 0.372%
>
> From 4/11 to 5/9 is $15.65 to $16.19, or 3.4505%
>
> Those are the two different total return numbers you're looking at.
>
> Manually adjust the starting date on the chart to 4/9 and you'll see a big
> drop on those first 2 days.
>
>
> On Sun, May 11, 2014 at 6:40 AM, andrei radulescu-banu
> bitdribble@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>> Randy, thanks for writing and maintaining a most excellent app!
>>
>> Trying to balance a 401k account here, and I have a newbie question for
>> the group about Morningstar total returns. Ticker is FMXKX. Looking at this
>> chart, clicking "1-Month", the total return is $10,345.05:
>>
>>
>> http://quote.morningstar.com/fund/chart.aspx?t=FMXKX®ion=usa&culture=en-US
>>
>> Looking at this URL, however, the 1-Month return is .37%:
>>
>> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=fmxkx
>>
>> I'm off by an order of magnitude, and I don't understand why. Obviously,
>> the 2nd URL is something the SMF tool can parse, thereby my interest.
>> (Well, in fact the SMF is parsing the total return instead from
>> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=FMXKX,
>> for efficiency, but that gives the same value .37%).
>>
>
>
>

--
==================================
Andrei Radulescu-Banu
86 Cedar St, Lexington MA
617.216.8509 (m), 781.862.5854 (h)
lex-wiki.org, lex4lang.org,
andrei4schools.com, bitdribble.com
==================================

Sun May 11, 2014 4:31 pm (PDT) . Posted by:

bablam

Hi Randy,


I need to run a model using the average price during the quarter for a large number of companies (500+) for the last eight quarters. Is there an easier way to do it that downloading the price history for each of those stocks?


Best,


Manish
Diposting oleh EXCEL FEED LEARNING di 16.31

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)