15 Messages
Digest #4230
Messages
Fri Nov 10, 2017 7:00 pm (PST) . Posted by:
"Thomas J Strouse" thomas.strouse@nielsen.com
Randy,
What a powerful function!! How do I make it variable? I receive 10 year
data when I use it. For example:
=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html?t=
<http://financials.morningstar.com/ajax/exportKR2CSV.html?t=AAPL>AAPL")
Growth Profitability and Financial Ratios for Apple Inc
Financials
2008-09 2009-09 2010-09 2011-09 2012-09 2013-09 2014-09 2015-09 2016-09
2017-09 TTM
Revenue USD Mil 37,491 42,905 65,225 108,249 156,508 170,910 182,795 233,715
215,639 229,234 229,234
Gross Margin % 35.2 40.1 39.4 40.5 43.9 37.6 38.6 40.1 39.1 38.5 38.5
Operating Income USD Mil 8,327 11,740 18,385 33,790 55,241 48,999 52,503
71,230 60,024 61,344 61,344
Operating Margin % 22.2 27.4 28.2 31.2 35.3 28.7 28.7 30.5 27.8 26.8 26.8
Net Income USD Mil 6,119 8,235 14,013 25,922 41,733 37,037 39,510 53,394
45,687 48,351 48,351
Earnings Per Share USD 0.97 1.3 2.16 3.95 6.31 5.68 6.45 9.22 8.31 9.21 9.21
However, when I use =smfGetCSVFile("
http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=
"&C2&"&reportType="&C3&"&period="&C4&"&dataType="&C5&"&order="&C6&"&columnYear="&C7&"&rounding=3&denominatorView="&C8)
to get MStar Income Statement, Balance Sheet and Cash Flow Statements, *I
only get 5 years of data*. I will still probably pay for the premium
package as I am calculating NOPAT and Invested Capital.
Another issue, I had to get a new laptop so I installed the macros
smfFixLinks and smfForceRecalculate. However, I have multiple tabs on my
spreadsheet and smfFixLinks seems to only fix the fields on the first tab.
The following tabs (not in sequence), still shows:
='C:\SMF\Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,
5465)
Any solutions?
Thanks
Tom
On Fri, Nov 10, 2017 at 8:05 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> I'm saying yes, but I'm not sure of the exact context for "that 10-year
> data".
>
> As a subscriber, this:
>
> =smfGetCSVFile("http://financials.morningstar.com/
> ajax/exportKR2CSV.html?t=MMM")
>
> ...currently returns me something like (only the first few lines shown):
>
> Growth Profitability and Financial Ratios for 3M Co
> Financials
> 2007-12 2008-12 2009-12 2010-12 2011-12 2012-12 2013-12 2014-12 2015-12
> 2016-12 TTM
> Revenue USD Mil 24462 25269 23123 26662 29611 29904 30871 31821 30274
> 30109 30996
> Gross Margin % 47.9 47.1 47.6 48.1 47 47.5 47.8 48.3 49.2 50 49.6
> Operating Income USD Mil 6193 5218 4814 5918 6178 6483 6666 7135 6946 7223
> 7664
> Operating Margin % 25.3 20.6 20.8 22.2 20.9 21.7 21.6 22.4 22.9 24 24.7
> Net Income USD Mil 4096 3460 3193 4085 4283 4444 4659 4956 4833 5050 5490
> Earnings Per Share USD 5.6 4.89 4.52 5.63 5.96 6.32 6.72 7.49 7.58 8.16
> 8.96
> Dividends USD 1.92 2 2.04 2.1 2.2 2.36 2.54 3.42 4.1 4.44 4.63
>
> I think if a non-subscriber tries that function, they'll only see five
> years?
>
> The above is just key ratios. There are similar CSV files for the
> financial statements data.
>
>
> On Fri, Nov 10, 2017 at 5:31 PM, thomas.strouse@
> ...
> wrote:
>
>>
>> If I buy the premium package from Morningstar to gain access to their
>> 10-year data, is it correct that I will be able to access that 10-year data
>> from the *existing *SMF functions?
>>
>>
>>
>
--
*Thomas J. Strouse*
VP and Senior Intellectual Property Counsel
The Nielsen Company
10 Waterview Blvd., Parsippany, NJ
Office (973) 299-2202
Mobile (973) 216-4631
Fax (973) 860-1637
www.nielsen.com
*The material in this transmission contains confidential information
intended only for the addressee. If you are not the addressee, any
disclosure or use of this information by you is strictly prohibited. If
you have received this transmission in error, please delete it, destroy all
copies, and notify The Nielsen Company by telephone at 973.299.2202. Thank
you*
What a powerful function!! How do I make it variable? I receive 10 year
data when I use it. For example:
=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html?t=
<http://financials.morningstar.com/ajax/exportKR2CSV.html?t=AAPL>AAPL")
Growth Profitability and Financial Ratios for Apple Inc
Financials
2008-09 2009-09 2010-09 2011-09 2012-09 2013-09 2014-09 2015-09 2016-09
2017-09 TTM
Revenue USD Mil 37,491 42,905 65,225 108,249 156,508 170,910 182,795 233,715
215,639 229,234 229,234
Gross Margin % 35.2 40.1 39.4 40.5 43.9 37.6 38.6 40.1 39.1 38.5 38.5
Operating Income USD Mil 8,327 11,740 18,385 33,790 55,241 48,999 52,503
71,230 60,024 61,344 61,344
Operating Margin % 22.2 27.4 28.2 31.2 35.3 28.7 28.7 30.5 27.8 26.8 26.8
Net Income USD Mil 6,119 8,235 14,013 25,922 41,733 37,037 39,510 53,394
45,687 48,351 48,351
Earnings Per Share USD 0.97 1.3 2.16 3.95 6.31 5.68 6.45 9.22 8.31 9.21 9.21
However, when I use =smfGetCSVFile("
http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=
"&C2&"&reportType="&C3&"&period="&C4&"&dataType="&C5&"&order="&C6&"&columnYear="&C7&"&rounding=3&denominatorView="&C8)
to get MStar Income Statement, Balance Sheet and Cash Flow Statements, *I
only get 5 years of data*. I will still probably pay for the premium
package as I am calculating NOPAT and Invested Capital.
Another issue, I had to get a new laptop so I installed the macros
smfFixLinks and smfForceRecalculate. However, I have multiple tabs on my
spreadsheet and smfFixLinks seems to only fix the fields on the first tab.
The following tabs (not in sequence), still shows:
='C:\SMF\Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,
5465)
Any solutions?
Thanks
Tom
On Fri, Nov 10, 2017 at 8:05 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> I'm saying yes, but I'm not sure of the exact context for "that 10-year
> data".
>
> As a subscriber, this:
>
> =smfGetCSVFile("http://financials.morningstar.com/
> ajax/exportKR2CSV.
>
> ...currently returns me something like (only the first few lines shown):
>
> Growth Profitability and Financial Ratios for 3M Co
> Financials
> 2007-12 2008-12 2009-12 2010-12 2011-12 2012-12 2013-12 2014-12 2015-12
> 2016-12 TTM
> Revenue USD Mil 24462 25269 23123 26662 29611 29904 30871 31821 30274
> 30109 30996
> Gross Margin % 47.9 47.1 47.6 48.1 47 47.5 47.8 48.3 49.2 50 49.6
> Operating Income USD Mil 6193 5218 4814 5918 6178 6483 6666 7135 6946 7223
> 7664
> Operating Margin % 25.3 20.6 20.8 22.2 20.9 21.7 21.6 22.4 22.9 24 24.7
> Net Income USD Mil 4096 3460 3193 4085 4283 4444 4659 4956 4833 5050 5490
> Earnings Per Share USD 5.6 4.89 4.52 5.63 5.96 6.32 6.72 7.49 7.58 8.16
> 8.96
> Dividends USD 1.92 2 2.04 2.1 2.2 2.36 2.54 3.42 4.1 4.44 4.63
>
> I think if a non-subscriber tries that function, they'll only see five
> years?
>
> The above is just key ratios. There are similar CSV files for the
> financial statements data.
>
>
> On Fri, Nov 10, 2017 at 5:31 PM, thomas.strouse@
> ...
> wrote:
>
>>
>> If I buy the premium package from Morningstar to gain access to their
>> 10-year data, is it correct that I will be able to access that 10-year data
>> from the *existing *SMF functions?
>>
>>
>>
>
--
*Thomas J. Strouse*
VP and Senior Intellectual Property Counsel
The Nielsen Company
10 Waterview Blvd., Parsippany, NJ
Office (973) 299-2202
Mobile (973) 216-4631
Fax (973) 860-1637
www.nielsen.
*The material in this transmission contains confidential information
intended only for the addressee. If you are not the addressee, any
disclosure or use of this information by you is strictly prohibited. If
you have received this transmission in error, please delete it, destroy all
copies, and notify The Nielsen Company by telephone at 973.299.2202. Thank
you*
Sat Nov 11, 2017 12:39 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Maybe everyone gets 10 years of the Key Ratios, but only subscribers get 10
years of the financial statements?
I don't know why you would "install
"
the macros smfFixLinks and smfForceRecalculation. They are part of the
add-in and are installed when it is? The one in the add-in cycles between
each existing worksheet. If you have one that does one sheet at a time,
you'd need to run it on each sheet?
On Fri, Nov 10, 2017 at 7:59 PM, Thomas J Strouse thomas.strouse@
....
wrote:
>
> What a powerful function!! How do I make it variable? I receive 10 year
> data when I use it. For example:
>
> =smfGetCSVFile("http://financials.morningstar.com/ajax/
> exportKR2CSV.html?t=
> <http://financials.morningstar.com/ajax/exportKR2CSV.html?t=AAPL>AAPL")
>
>
> Growth Profitability and Financial Ratios for Apple Inc
> Financials
> 2008-09 2009-09 2010-09 2011-09 2012-09 2013-09 2014-09 2015-09 2016-09
> 2017-09 TTM
> Revenue USD Mil 37,491 42,905 65,225 108,249 156,508 170,910 182,795
> 233,715 215,639 229,234 229,234
> Gross Margin % 35.2 40.1 39.4 40.5 43.9 37.6 38.6 40.1 39.1 38.5 38.5
> Operating Income USD Mil 8,327 11,740 18,385 33,790 55,241 48,999 52,503
> 71,230 60,024 61,344 61,344
> Operating Margin % 22.2 27.4 28.2 31.2 35.3 28.7 28.7 30.5 27.8 26.8 26.8
> Net Income USD Mil 6,119 8,235 14,013 25,922 41,733 37,037 39,510 53,394
> 45,687 48,351 48,351
> Earnings Per Share USD 0.97 1.3 2.16 3.95 6.31 5.68 6.45 9.22 8.31 9.21
> 9.21
>
>
> However, when I use =smfGetCSVFile("http://financials.morningstar.com/
> ajax/ReportProcess4CSV.html?t="&C2&"&reportType="&C3&"&
> period="&C4&"&dataType="&C5&"&order="&C6&"&columnYear="&C7&"
> &rounding=3&denominatorView="&C8)
>
> to get MStar Income Statement, Balance Sheet and Cash Flow Statements, *I
> only get 5 years of data*. I will still probably pay for the premium
> package as I am calculating NOPAT and Invested Capital.
>
>
> Another issue, I had to get a new laptop so I installed
>
> the macros smfFixLinks and smfForceRecalculate. However, I have multiple
> tabs on my spreadsheet and smfFixLinks seems to only fix the fields on
> the first tab. The following tabs (not in sequence), still shows:
>
> ='C:\SMF\Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,
> 5465)
>
> Any solutions?
>
years of the financial statements?
I don't know why you would "install
"
the macros smfFixLinks and smfForceRecalculation. They are part of the
add-in and are installed when it is? The one in the add-in cycles between
each existing worksheet. If you have one that does one sheet at a time,
you'd need to run it on each sheet?
On Fri, Nov 10, 2017 at 7:59 PM, Thomas J Strouse thomas.strouse@
....
wrote:
>
> What a powerful function!! How do I make it variable? I receive 10 year
> data when I use it. For example:
>
> =smfGetCSVFile("http://financials.morningstar.com/ajax/
> exportKR2CSV.html?t=
> <http://financials.morningstar.com/ajax/exportKR2CSV.html?t=AAPL>AAPL")
>
>
> Growth Profitability and Financial Ratios for Apple Inc
> Financials
> 2008-09 2009-09 2010-09 2011-09 2012-09 2013-09 2014-09 2015-09 2016-09
> 2017-09 TTM
> Revenue USD Mil 37,491 42,905 65,225 108,249 156,508 170,910 182,795
> 233,715 215,639 229,234 229,234
> Gross Margin % 35.2 40.1 39.4 40.5 43.9 37.6 38.6 40.1 39.1 38.5 38.5
> Operating Income USD Mil 8,327 11,740 18,385 33,790 55,241 48,999 52,503
> 71,230 60,024 61,344 61,344
> Operating Margin % 22.2 27.4 28.2 31.2 35.3 28.7 28.7 30.5 27.8 26.8 26.8
> Net Income USD Mil 6,119 8,235 14,013 25,922 41,733 37,037 39,510 53,394
> 45,687 48,351 48,351
> Earnings Per Share USD 0.97 1.3 2.16 3.95 6.31 5.68 6.45 9.22 8.31 9.21
> 9.21
>
>
> However, when I use =smfGetCSVFile("http://financials.morningstar.com/
> ajax/ReportProcess4
> period="
> &rounding=3&denomin
>
> to get MStar Income Statement, Balance Sheet and Cash Flow Statements, *I
> only get 5 years of data*. I will still probably pay for the premium
> package as I am calculating NOPAT and Invested Capital.
>
>
> Another issue, I had to get a new laptop so I installed
>
> the macros smfFixLinks and smfForceRecalculate
> tabs on my spreadsheet and smfFixLinks seems to only fix the fields on
> the first tab. The following tabs (not in sequence), still shows:
>
> ='C:\
> 5465)
>
> Any solutions?
>
Sat Nov 11, 2017 6:08 am (PST) . Posted by:
"Thomas J Strouse" thomas.strouse@nielsen.com
Randy,
I think you're right. MStar allows all to access some 10-year data, e.g.,
Ratios data, but not other 10-year data.
As for smfFixlinks and smfForceRecalculation, I watched the video in the
Top Level Directory entitled, "YouTube video on how to use the smfFixLinks
macro to remove the hard coded location in SMF add-in templates" a year
ago, so had these macros installed. I remove the macros, and with some
work (updated the link source), I was able to get the add-in to work
properly.
Again, thanks for all your help!
Tom
On Sat, Nov 11, 2017 at 3:39 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Maybe everyone gets 10 years of the Key Ratios, but only subscribers get
> 10 years of the financial statements?
>
> I don't know why you would "install
> "
> the macros smfFixLinks and smfForceRecalculation. They are part of the
> add-in and are installed when it is? The one in the add-in cycles between
> each existing worksheet. If you have one that does one sheet at a time,
> you'd need to run it on each sheet?
>
>
> On Fri, Nov 10, 2017 at 7:59 PM, Thomas J Strouse thomas.strouse@
> ....
> wrote:
>
>>
>> What a powerful function!! How do I make it variable? I receive 10 year
>> data when I use it. For example:
>>
>> =smfGetCSVFile("http://financials.morningstar.com/ajax/expor
>> tKR2CSV.html?t=
>> <http://financials.morningstar.com/ajax/exportKR2CSV.html?t=AAPL>AAPL")
>>
>>
>> Growth Profitability and Financial Ratios for Apple Inc
>> Financials
>> 2008-09 2009-09 2010-09 2011-09 2012-09 2013-09 2014-09 2015-09 2016-09
>> 2017-09 TTM
>> Revenue USD Mil 37,491 42,905 65,225 108,249 156,508 170,910 182,795
>> 233,715 215,639 229,234 229,234
>> Gross Margin % 35.2 40.1 39.4 40.5 43.9 37.6 38.6 40.1 39.1 38.5 38.5
>> Operating Income USD Mil 8,327 11,740 18,385 33,790 55,241 48,999 52,503
>> 71,230 60,024 61,344 61,344
>> Operating Margin % 22.2 27.4 28.2 31.2 35.3 28.7 28.7 30.5 27.8 26.8 26.8
>> Net Income USD Mil 6,119 8,235 14,013 25,922 41,733 37,037 39,510 53,394
>> 45,687 48,351 48,351
>> Earnings Per Share USD 0.97 1.3 2.16 3.95 6.31 5.68 6.45 9.22 8.31 9.21
>> 9.21
>>
>>
>> However, when I use =smfGetCSVFile("http://fin
>> ancials.morningstar.com/ajax/ReportProcess4CSV.html?t="&C2&
>> "&reportType="&C3&"&period="&C4&"&dataType="&C5&"&order="&
>> C6&"&columnYear="&C7&"&rounding=3&denominatorView="&C8)
>>
>> to get MStar Income Statement, Balance Sheet and Cash Flow Statements, *I
>> only get 5 years of data*. I will still probably pay for the premium
>> package as I am calculating NOPAT and Invested Capital.
>>
>>
>> Another issue, I had to get a new laptop so I installed
>>
>> the macros smfFixLinks and smfForceRecalculate. However, I have multiple
>> tabs on my spreadsheet and smfFixLinks seems to only fix the fields on
>> the first tab. The following tabs (not in sequence), still shows:
>>
>> ='C:\SMF\Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,
>> 5465)
>>
>> Any solutions?
>>
>
>
--
*Thomas J. Strouse*
VP and Senior Intellectual Property Counsel
The Nielsen Company
10 Waterview Blvd., Parsippany, NJ
Office (973) 299-2202
Mobile (973) 216-4631
Fax (973) 860-1637
www.nielsen.com
*The material in this transmission contains confidential information
intended only for the addressee. If you are not the addressee, any
disclosure or use of this information by you is strictly prohibited. If
you have received this transmission in error, please delete it, destroy all
copies, and notify The Nielsen Company by telephone at 973.299.2202. Thank
you*
I think you're right. MStar allows all to access some 10-year data, e.g.,
Ratios data, but not other 10-year data.
As for smfFixlinks and smfForceRecalculation, I watched the video in the
Top Level Directory entitled, "YouTube video on how to use the smfFixLinks
macro to remove the hard coded location in SMF add-in templates" a year
ago, so had these macros installed. I remove the macros, and with some
work (updated the link source), I was able to get the add-in to work
properly.
Again, thanks for all your help!
Tom
On Sat, Nov 11, 2017 at 3:39 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Maybe everyone gets 10 years of the Key Ratios, but only subscribers get
> 10 years of the financial statements?
>
> I don't know why you would "install
> "
> the macros smfFixLinks and smfForceRecalculation. They are part of the
> add-in and are installed when it is? The one in the add-in cycles between
> each existing worksheet. If you have one that does one sheet at a time,
> you'd need to run it on each sheet?
>
>
> On Fri, Nov 10, 2017 at 7:59 PM, Thomas J Strouse thomas.strouse@
> ....
> wrote:
>
>>
>> What a powerful function!! How do I make it variable? I receive 10 year
>> data when I use it. For example:
>>
>> =smfGetCSVFile("http://financials.morningstar.com/ajax/expor
>> tKR2CSV.html?t=
>> <http://financials.morningstar.com/ajax/exportKR2CSV.html?t=AAPL>AAPL")
>>
>>
>> Growth Profitability and Financial Ratios for Apple Inc
>> Financials
>> 2008-09 2009-09 2010-09 2011-09 2012-09 2013-09 2014-09 2015-09 2016-09
>> 2017-09 TTM
>> Revenue USD Mil 37,491 42,905 65,225 108,249 156,508 170,910 182,795
>> 233,715 215,639 229,234 229,234
>> Gross Margin % 35.2 40.1 39.4 40.5 43.9 37.6 38.6 40.1 39.1 38.5 38.5
>> Operating Income USD Mil 8,327 11,740 18,385 33,790 55,241 48,999 52,503
>> 71,230 60,024 61,344 61,344
>> Operating Margin % 22.2 27.4 28.2 31.2 35.3 28.7 28.7 30.5 27.8 26.8 26.8
>> Net Income USD Mil 6,119 8,235 14,013 25,922 41,733 37,037 39,510 53,394
>> 45,687 48,351 48,351
>> Earnings Per Share USD 0.97 1.3 2.16 3.95 6.31 5.68 6.45 9.22 8.31 9.21
>> 9.21
>>
>>
>> However, when I use =smfGetCSVFile("http://fin
>> ancials.morningstar
>> "&reportType=
>> C6&"&columnYea
>>
>> to get MStar Income Statement, Balance Sheet and Cash Flow Statements, *I
>> only get 5 years of data*. I will still probably pay for the premium
>> package as I am calculating NOPAT and Invested Capital.
>>
>>
>> Another issue, I had to get a new laptop so I installed
>>
>> the macros smfFixLinks and smfForceRecalculate
>> tabs on my spreadsheet and smfFixLinks seems to only fix the fields on
>> the first tab. The following tabs (not in sequence), still shows:
>>
>> ='C:\
>> 5465)
>>
>> Any solutions?
>>
>
>
--
*Thomas J. Strouse*
VP and Senior Intellectual Property Counsel
The Nielsen Company
10 Waterview Blvd., Parsippany, NJ
Office (973) 299-2202
Mobile (973) 216-4631
Fax (973) 860-1637
www.nielsen.
*The material in this transmission contains confidential information
intended only for the addressee. If you are not the addressee, any
disclosure or use of this information by you is strictly prohibited. If
you have received this transmission in error, please delete it, destroy all
copies, and notify The Nielsen Company by telephone at 973.299.2202. Thank
you*
Fri Nov 10, 2017 7:24 pm (PST) . Posted by:
dmohn1
Randy, I found a work around for this issue in post 24211 "Use add-in from non add-in enabled Excel"
I followed your instructions to turn the add-in into a workbook and then I no longer need to use the add-in.
Thanks you for sharing this app and all your support.
I followed your instructions to turn the add-in into a workbook and then I no longer need to use the add-in.
Thanks you for sharing this app and all your support.
Sat Nov 11, 2017 12:44 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Several potential problems with that:
1. It makes it difficult to update the add-in code.
2. It only works in the one workbook
3. It makes your workbook much larger.
4. The add-in does require some external files for all functions to work.
RCHGetElementNumber(), for example.
On Fri, Nov 10, 2017 at 8:24 PM, dmohn1@
...
wrote:
> Randy, I found a work around for this issue in post
>
> 24211 "Use add-in from non add-in enabled Excel"
>
> I followed your instructions to turn the add-in into a workbook and then I
> no longer need to use the add-in.
>
> Thanks you for sharing this app and all your support.
1. It makes it difficult to update the add-in code.
2. It only works in the one workbook
3. It makes your workbook much larger.
4. The add-in does require some external files for all functions to work.
RCHGetElementNumber
On Fri, Nov 10, 2017 at 8:24 PM, dmohn1@
...
wrote:
> Randy, I found a work around for this issue in post
>
> 24211 "Use add-in from non add-in enabled Excel"
>
> I followed your instructions to turn the add-in into a workbook and then I
> no longer need to use the add-in.
>
> Thanks you for sharing this app and all your support.
Fri Nov 10, 2017 8:20 pm (PST) . Posted by:
eremon9
Randy: "reset sWebCache to "Y" at the end of your subroutine"
Got it. Thanks for the heads-up, Randy. Before installing the 11-8 version of your smf, Intraday recalc of my Port Mgr had been taking about 11 seconds, including a call to my own RecalcPortView. After installing the latest version of your smf earlier today, that jumped to about 25 minutes, with errors. I was mystified. Upon seeing your remarks about sWebCache and making the adjustment, recalc returned to the norm. What a relief!
I've revised RecalcPortView as you advised, and generalized it to accommodate Yahoo, BarCharts and whatever views and subviews may evolve over time (rngView refers to a discrete PortView retrieval array):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub RecalcPortView(ByVal rngView As Range)
sWebCache = "N"
With rngView
.Dirty
On Error Resume Next
.Calculate
End With
sWebCache = "Y"
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I'm considering the desirability of reducing the impact of these calls by minimizing the packet-size of more frequently executed calculations. I'd appreciate your thoughts on that.
Up to now I've timed my initial daily recalc for earliest retrieval of all available data, invoking smfForceRecalculation(), rchGetElementNumber(), smfGetYahooHistory(), a number of PortViews and a plethora of dependent formulas and displays—essentially refreshing the entire PortMgr. Subsequent intra-day recalcs retrieve the subset of that totality that's thought to meaningfully change during the course of a trading day (evading smfForceRecalculation).
Of late I've been wondering whether there's any percentage in breaking these calls down further, in the interests of reducing burden upon data providers and shrinking local processing time. For my style of investing, which is focused on the long-term evolution of fundamental values rather than on near-term price movements, there are a number of rchGetElementNumber() and PortView items that could just as well be updated weekly, monthly or perhaps even quarterly. As obvious examples, how often do you have to update sector, industry and company name? Even ratios like price versus earnings, sales, book, and cash flow, as well as ROE, ROIC and so forth can employ prior close as price, thereby limiting retrieval to once daily first thing, without meaningfully impacting present decision-making.
The main challenge has been the limited reliability of timely prices. The less reliable the information, the more redundant must be the formulas that rely upon it. With fingers crossed, I must say that PortView appears to reliably deliver accurate prices in real-time.
My main concern has been about a three hour delay in the receipt of present and prior close prices for WFC-PL (held as cash in the event of a 3-6 year market slump, in the meantime earning a 6.9% perpetual dividend). I presume this delay is some sort of Yahoo glitch related to preferreds. I can certainly live with the modest delay in the case of one stock whose purpose entirely divorces it from the near-term vagaries of the market. The problem is that it requires retrieval of prior closing prices, and shenanigans are disallowed in array retrieval. So the workaround introduces an inefficiency.
In any event, I'm wondering whether there's any percentage in pursuing this avenue of optimization.
Got it. Thanks for the heads-up, Randy. Before installing the 11-8 version of your smf, Intraday recalc of my Port Mgr had been taking about 11 seconds, including a call to my own RecalcPortView. After installing the latest version of your smf earlier today, that jumped to about 25 minutes, with errors. I was mystified. Upon seeing your remarks about sWebCache and making the adjustment, recalc returned to the norm. What a relief!
I've revised RecalcPortView as you advised, and generalized it to accommodate Yahoo, BarCharts and whatever views and subviews may evolve over time (rngView refers to a discrete PortView retrieval array):
~~~~~~~~~~~~
Public Sub RecalcPortView(
sWebCache = "N"
With rngView
.Dirty
On Error Resume Next
.Calculate
End With
sWebCache = "Y"
End Sub
~~~~~~~~~~~~
I'm considering the desirability of reducing the impact of these calls by minimizing the packet-size of more frequently executed calculations. I'd appreciate your thoughts on that.
Up to now I've timed my initial daily recalc for earliest retrieval of all available data, invoking smfForceRecalculati
Of late I've been wondering whether there's any percentage in breaking these calls down further, in the interests of reducing burden upon data providers and shrinking local processing time. For my style of investing, which is focused on the long-term evolution of fundamental values rather than on near-term price movements, there are a number of rchGetElementNumber
The main challenge has been the limited reliability of timely prices. The less reliable the information, the more redundant must be the formulas that rely upon it. With fingers crossed, I must say that PortView appears to reliably deliver accurate prices in real-time.
My main concern has been about a three hour delay in the receipt of present and prior close prices for WFC-PL (held as cash in the event of a 3-6 year market slump, in the meantime earning a 6.9% perpetual dividend). I presume this delay is some sort of Yahoo glitch related to preferreds. I can certainly live with the modest delay in the case of one stock whose purpose entirely divorces it from the near-term vagaries of the market. The problem is that it requires retrieval of prior closing prices, and shenanigans are disallowed in array retrieval. So the workaround introduces an inefficiency.
In any event, I'm wondering whether there's any percentage in pursuing this avenue of optimization.
Sat Nov 11, 2017 1:27 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Y'know, if you're going to write VBA code to update things, I might
recommend just writing the results of the function into the worksheet as
values. For example:
sWebCache = "N"
Range("rDataRange") = smfGetYahooPortfolioView(Range("rTickerList"),
Range("rFieldList"), , 1, Range("rTickerList";).Count + 1,
Range("rFieldList").Count)
sWebCache = "Y"
Then you have values instead of formulas that calculate. However, it would
mean that smfForceRecalculation would not update them either. Maybe add a
cell with a time stamp in it, so you know when it was last updated...?
The RCHGetElementNumber() items can be selectively updated in a table using
the smfUpdateDownloadTable macro. Maybe a different table for each need. As
you mention, sector, industry group, and names don't change often.
On Fri, Nov 10, 2017 at 9:20 PM, tamurphy@
...
wrote:
>
> Randy: "*reset sWebCache to "Y" at the end of your subroutine"*
>
> *Got it. Thanks for the heads-up, Randy. Before **installing the 11-8 **version
> of your **smf, **Intraday recalc of my Port Mgr had been taking about 11
> seconds, including a call to my own RecalcPortView. After installing the
> latest **version of your **smf earlier today, that jumped to about 25
> minutes, with errors. I was mystified. Upon seeing your remarks about
> sWebCache and making the adjustment, recalc returned to the norm. What a
> relief!*
>
> *I've revised* *RecalcPortView** as you advised, and generalized** it to
> accommodate Yahoo, BarCharts and whatever views and subviews may evolve
> over time (rngView refers to a discrete PortView retrieval array):*
>
> *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*
>
> *Public Sub RecalcPortView(ByVal rngView As Range)*
> * sWebCache = "N"*
> * With rngView*
> * .Dirty*
> * On Error Resume Next*
> * .Calculate*
> * End With*
> * sWebCache = "Y"*
> *End Sub*
>
> *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*
>
> *I'm considering the desirability of **reducing **the impact of these
> calls by **minimizing **the packet-size of more frequently executed
> calculations. I'd appreciate your thoughts on that. *
>
> *Up to now I've timed my initial daily recalc for earliest retrieval of
> all available **data, invoking** smfForceRecalculation(),
> rchGetElementNumber(), smfGetYahooHistory(), **a number of PortViews **and
> a plethora of dependent formulas and displays—essentially refreshing the
> entire PortMgr. Subsequent intra-day recalcs retrieve the subset of that
> totality that's thought to meaningfully change during the course of a
> trading day (evading smfForceRecalculation). *
>
> *Of late I've been wondering whether there's any percentage in breaking
> these calls down further, in the interests of reducing **burden upon data
> providers and** shrinking local processing time. For my style of
> investing, which is focused on the long-term evolution of fundamental
> values rather than on near-term price movements, there are a number of
> rchGetElementNumber() and PortView items that could just as well be updated
> weekly, monthly or perhaps even quarterly. As obvious examples, how often
> do you have to update sector, industry and company name? Even ratios like
> price versus earnings, sales, book, and cash flow, as well as ROE, ROIC
> and so forth can employ prior close as price**, thereby limiting
> retrieval to once daily first thing,** without meaningfully impacting
> present decision-making. *
>
> *The main challenge has been the limited reliability of timely prices.** The
> less reliable the information, the more redundant must be the formulas that
> rely upon it.** With fingers crossed, I must say that PortView appears to
> reliably deliver accurate** prices in** real-time. *
>
> *My main concern has been about a three hour delay in the receipt of
> present and prior close prices for WFC-PL (held as cash in the event of a
> 3-6 year market slump, in the meantime earning a 6.9% perpetual dividend).
> I presume this delay is some sort of Yahoo glitch related to preferreds. I
> can certainly live with the modest delay in the case of one stock whose
> purpose entirely divorces it from the near-term vagaries of the market. The
> problem is that it requires retrieval of prior closing prices, and*
> * shenanigans** are disallowed in array retrieval. So the workaround
> introduces an inefficiency. *
>
> *In any event, I'm wondering whether there's any percentage in pursuing
> this avenue of optimization. *
>
recommend just writing the results of the function into the worksheet as
values. For example:
sWebCache = "N"
Range("
Range("
Range("
sWebCache = "Y"
Then you have values instead of formulas that calculate. However, it would
mean that smfForceRecalculati
cell with a time stamp in it, so you know when it was last updated...?
The RCHGetElementNumber
the smfUpdateDownloadTa
you mention, sector, industry group, and names don't change often.
On Fri, Nov 10, 2017 at 9:20 PM, tamurphy@
...
wrote:
>
> Randy: "*reset sWebCache to "Y" at the end of your subroutine"
>
> *Got it. Thanks for the heads-up, Randy. Before **installing the 11-8 **version
> of your **smf, **Intraday recalc of my Port Mgr had been taking about 11
> seconds, including a call to my own RecalcPortView. After installing the
> latest **version of your **smf earlier today, that jumped to about 25
> minutes, with errors. I was mystified. Upon seeing your remarks about
> sWebCache and making the adjustment, recalc returned to the norm. What a
> relief!*
>
> *I've revised* *RecalcPortView*
> accommodate Yahoo, BarCharts and whatever views and subviews may evolve
> over time (rngView refers to a discrete PortView retrieval array):*
>
> *~~~~~~~~~~~
>
> *Public Sub RecalcPortView(
> * sWebCache = "N"
> * With rngView*
> * .Dirty*
> * On Error Resume Next*
> * .Calculate*
> * End With*
> * sWebCache = "Y"
> *End Sub*
>
> *~~~~~~~~~~~
>
> *I'm considering the desirability of **reducing **the impact of these
> calls by **minimizing **the packet-size of more frequently executed
> calculations. I'd appreciate your thoughts on that. *
>
> *Up to now I've timed my initial daily recalc for earliest retrieval of
> all available **data, invoking** smfForceRecalculati
> rchGetElementNumber
> a plethora of dependent formulas and displays—essentially refreshing the
> entire PortMgr. Subsequent intra-day recalcs retrieve the subset of that
> totality that's thought to meaningfully change during the course of a
> trading day (evading smfForceRecalculati
>
> *Of late I've been wondering whether there's any percentage in breaking
> these calls down further, in the interests of reducing **burden upon data
> providers and** shrinking local processing time. For my style of
> investing, which is focused on the long-term evolution of fundamental
> values rather than on near-term price movements, there are a number of
> rchGetElementNumber
> weekly, monthly or perhaps even quarterly. As obvious examples, how often
> do you have to update sector, industry and company name? Even ratios like
> price versus earnings, sales, book, and cash flow, as well as ROE, ROIC
> and so forth can employ prior close as price**, thereby limiting
> retrieval to once daily first thing,** without meaningfully impacting
> present decision-making. *
>
> *The main challenge has been the limited reliability of timely prices.** The
> less reliable the information, the more redundant must be the formulas that
> rely upon it.** With fingers crossed, I must say that PortView appears to
> reliably deliver accurate** prices in** real-time. *
>
> *My main concern has been about a three hour delay in the receipt of
> present and prior close prices for WFC-PL (held as cash in the event of a
> 3-6 year market slump, in the meantime earning a 6.9% perpetual dividend).
> I presume this delay is some sort of Yahoo glitch related to preferreds. I
> can certainly live with the modest delay in the case of one stock whose
> purpose entirely divorces it from the near-term vagaries of the market. The
> problem is that it requires retrieval of prior closing prices, and*
> * shenanigans*
> introduces an inefficiency. *
>
> *In any event, I'm wondering whether there's any percentage in pursuing
> this avenue of optimization. *
>
Fri Nov 10, 2017 8:28 pm (PST) . Posted by:
zbgb952
Yes, I know I asked this before, but I could not find it at your website.
ZGB
ZGB
Fri Nov 10, 2017 9:32 pm (PST) . Posted by:
zbgb952
Hello Randy..
Now I am more confused.
Can the function:
RCHGetTableCell function
Be used to do this?..if so, Please show me how to get the 'PRICE' and the Finviz.com website as I previously asked. I don't care if it has to go to a different web page for each ticker.
It is more work to figure out how to download a file and all that... I already have lots of files I have to deal with.
thanks.
ZBGB
Now I am more confused.
Can the function:
RCHGetTableCell function
Be used to do this?..if so, Please show me how to get the 'PRICE&#
It is more work to figure out how to download a file and all that... I already have lots of files I have to deal with.
thanks.
ZBGB
Fri Nov 10, 2017 10:03 pm (PST) . Posted by:
zbgb952
Hello Randy...never mind my last response...I just found it. Thanks.
Sat Nov 11, 2017 12:33 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Attached is what I came up with. Only took a few minutes. The first three
tickers didn't exist -- doing a lookup on them finds them under a different
security identification field.
I moved the "t=" parameter to the end of the parameter string. It makes
concatenation easier, as you only need one instead of two.
On Fri, Nov 10, 2017 at 6:49 PM, dahook@
...
wrote:
>
> It's amazing, during all the furor over the function disabling, that you
> can spend so much time helping. While I'm pretty familiar with Excel,
> macros are a complete unknown for me, and no matter what I've done to the
> URL callout, I can't get the concatenation right, so that the cell with FIG
> or MAW104 code gets referenced for the web address. Listing the two page
> calls with the embedded strings to be replaced by a Cell reference, and
> hope you can assist with building a working URL for it.
>
> After the second section of the URL, the Column A absolute cell references
> are a lookup to the field contents, starting with Fixed Income, Canadian
> Equity, etc., as posted in my last message.
>
> Thanx for any solutions you can offer:
>
> =RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/ETF/etf_
> Portfolionew.aspx?t=FIG®ion=CAN&culture=en-CA",1,$A$8,$A$9,$A$6)
> =RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/fund/
> PortfolioOverviewNew.aspx?t=0P0000714D®ion=CAN&culture=en-CA
> ",1,$A$11,$A$7)
>
> *Fixed Income*
> *Canadian Equity*
> *U.S. Equity*
> *International Equity*
> *Other*
> *Cash*
> *Total*
>
tickers didn't exist -- doing a lookup on them finds them under a different
security identification field.
I moved the "t=" parameter to the end of the parameter string. It makes
concatenation easier, as you only need one instead of two.
On Fri, Nov 10, 2017 at 6:49 PM, dahook@
...
wrote:
>
> It's amazing, during all the furor over the function disabling, that you
> can spend so much time helping. While I'm pretty familiar with Excel,
> macros are a complete unknown for me, and no matter what I've done to the
> URL callout, I can't get the concatenation right, so that the cell with FIG
> or MAW104 code gets referenced for the web address. Listing the two page
> calls with the embedded strings to be replaced by a Cell reference, and
> hope you can assist with building a working URL for it.
>
> After the second section of the URL, the Column A absolute cell references
> are a lookup to the field contents, starting with Fixed Income, Canadian
> Equity, etc., as posted in my last message.
>
> Thanx for any solutions you can offer:
>
> =RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/ETF/etf_
> Portfolionew.aspx?t=FIG®ion=CAN&culture=en-CA",1,$A$8,$A$9,$A$6)
> =RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/fund/
> PortfolioOverviewNe
> ",1,$A$
>
> *Fixed Income*
> *Canadian Equity*
> *U.S. Equity*
> *International Equity*
> *Other*
> *Cash*
> *Total*
>
Attachment(s) from Randy Harmelink
1 of 1 File(s)
Sat Nov 11, 2017 10:11 am (PST) . Posted by:
davie_001
Randy,
(Just lost a huge message when the upload of the attachment failed.)
You are a true wizard, and thanks for concat lesson, though have no idea how to turn it into a single reference. Your knowledge will greatly help in future usage of SMF, which is even better news.
I did know that the MF symbol was not a true ticker, as it was evident from the URLs I sent you, what needed to be used. You employed the ETF page call, and it worked, despite the MF one appearing when I went to the website to find the asset breakdown table. This is a big plus, as the grid can now use a common callout regardless of ETF or MF.
For brevity I defined the web string as a name, in Cell $B$2, and followed your syntax for the rest. Didn't understand the ">" symbol and the concat with the Asset Class cell.
Alas, the callout for the three MF did not pick up the values for Canadian and International. In my slogging for hours last night, I found it necessary to refer to "Cat Avg" and use that as a Find entry, adding the Asset Class cell as a second field. Now the data fetch got the numbers right.
This means, every column in the table can be copied and pasted from the definitions in Column C. Just add the correct ticker to Row 5, and future maintenance is a snap.
You are my hero and my massive portfolio spreadsheet can use this feed immediately to chart my holdings.
Thanx,
Dave...
(Just lost a huge message when the upload of the attachment failed.)
You are a true wizard, and thanks for concat lesson, though have no idea how to turn it into a single reference. Your knowledge will greatly help in future usage of SMF, which is even better news.
I did know that the MF symbol was not a true ticker, as it was evident from the URLs I sent you, what needed to be used. You employed the ETF page call, and it worked, despite the MF one appearing when I went to the website to find the asset breakdown table. This is a big plus, as the grid can now use a common callout regardless of ETF or MF.
For brevity I defined the web string as a name, in Cell $B$2, and followed your syntax for the rest. Didn't understand the ">" symbol and the concat with the Asset Class cell.
Alas, the callout for the three MF did not pick up the values for Canadian and International. In my slogging for hours last night, I found it necessary to refer to "Cat Avg" and use that as a Find entry, adding the Asset Class cell as a second field. Now the data fetch got the numbers right.
This means, every column in the table can be copied and pasted from the definitions in Column C. Just add the correct ticker to Row 5, and future maintenance is a snap.
You are my hero and my massive portfolio spreadsheet can use this feed immediately to chart my holdings.
Thanx,
Dave...
Attachment(s) from
1 of 1 File(s)
Sat Nov 11, 2017 10:24 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Re:
Didn't understand the ">" symbol and the concat with the Asset Class cell.
It just makes the search within the source code of the web page more
unique. The ">" is just the ending byte of the HTML tag preceding the
search term. Otherwise, using common words or phrases can find the search
term in earlier parts of the web page, sometimes even in the META tags for
the web page. The technique doesn't always work, because it depends on how
the web page is coded. But it does work quite often.
Re:
I found it necessary to refer to "Cat Avg" and use that as a Find entry
At times, it can be challenging to find out which patterns work best.
------------------------------
For Morningstar, the "fund" pages often work for ETFs, but not always.
BTW -- to see more columns on a web page, I would just take the right-most
7 characters of the "As of" field. If I do that on each one and auto-fit
columns, everything is easily visible on my display.
On Sat, Nov 11, 2017 at 11:11 AM, dahook@
...
wrote:
>
> (Just lost a huge message when the upload of the attachment failed.)
>
> You are a true wizard, and thanks for concat lesson, though have no idea
> how to turn it into a single reference. Your knowledge will greatly help in
> future usage of SMF, which is even better news.
>
> I did know that the MF symbol was not a true ticker, as it was evident
> from the URLs I sent you, what needed to be used. You employed the ETF page
> call, and it worked, despite the MF one appearing when I went to the
> website to find the asset breakdown table. This is a big plus, as the grid
> can now use a common callout regardless of ETF or MF.
>
> For brevity I defined the web string as a name, in Cell $B$2, and followed
> your syntax for the rest.
>
> Didn't understand the ">" symbol and the concat with the Asset Class cell.
>
> Alas, the callout for the three MF did not pick up the values for Canadian
> and International. In my slogging for hours last night,
>
> I found it necessary to refer to "Cat Avg" and use that as a Find entry,
> adding the Asset Class cell as a second field. Now the data fetch got the
> numbers right.
>
> This means, every column in the table can be copied and pasted from the
> definitions in Column C. Just add the correct ticker to Row 5, and future
> maintenance is a snap.
>
> You are my hero and my massive portfolio spreadsheet can use this feed
> immediately to chart my holdings.
>
>
>
Didn't understand the ">" symbol and the concat with the Asset Class cell.
It just makes the search within the source code of the web page more
unique. The ">" is just the ending byte of the HTML tag preceding the
search term. Otherwise, using common words or phrases can find the search
term in earlier parts of the web page, sometimes even in the META tags for
the web page. The technique doesn't always work, because it depends on how
the web page is coded. But it does work quite often.
Re:
I found it necessary to refer to "Cat Avg" and use that as a Find entry
At times, it can be challenging to find out which patterns work best.
------------
For Morningstar, the "fund" pages often work for ETFs, but not always.
BTW -- to see more columns on a web page, I would just take the right-most
7 characters of the "As of" field. If I do that on each one and auto-fit
columns, everything is easily visible on my display.
On Sat, Nov 11, 2017 at 11:11 AM, dahook@
...
wrote:
>
> (Just lost a huge message when the upload of the attachment failed.)
>
> You are a true wizard, and thanks for concat lesson, though have no idea
> how to turn it into a single reference. Your knowledge will greatly help in
> future usage of SMF, which is even better news.
>
> I did know that the MF symbol was not a true ticker, as it was evident
> from the URLs I sent you, what needed to be used. You employed the ETF page
> call, and it worked, despite the MF one appearing when I went to the
> website to find the asset breakdown table. This is a big plus, as the grid
> can now use a common callout regardless of ETF or MF.
>
> For brevity I defined the web string as a name, in Cell $B$2, and followed
> your syntax for the rest.
>
> Didn't understand the ">" symbol and the concat with the Asset Class cell.
>
> Alas, the callout for the three MF did not pick up the values for Canadian
> and International. In my slogging for hours last night,
>
> I found it necessary to refer to "Cat Avg" and use that as a Find entry,
> adding the Asset Class cell as a second field. Now the data fetch got the
> numbers right.
>
> This means, every column in the table can be copied and pasted from the
> definitions in Column C. Just add the correct ticker to Row 5, and future
> maintenance is a snap.
>
> You are my hero and my massive portfolio spreadsheet can use this feed
> immediately to chart my holdings.
>
>
>
Sat Nov 11, 2017 7:54 am (PST) . Posted by:
pchek
Randy
Thank you for sharing your fantastic macros. Just converted to smfGetYahooPortfolioView and it works like a charm.
Thanks Again.
Paul
Thank you for sharing your fantastic macros. Just converted to smfGetYahooPortfoli
Thanks Again.
Paul
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar