Sabtu, 08 Juli 2017

[smf_addin] Digest Number 4085

5 Messages

Digest #4085

Messages

Fri Jul 7, 2017 10:00 pm (PDT) . Posted by:

mikemcq802

I'm a subscriber to M* and for several years now I have been retrieving 10 years of financial statements in Excel via SMF (Income, Balance Sheet, Cash Flow). Every now and again Excel "forgets" that I am an M* subscriber and I had to use the Excel Data/FromWeb function to logon to M* from within Excel. No problem.

But, today, I saw I only got 5 years of data which is the default for non-subscribers. So I went to logon through Excel. But, Excel's Data/FromWeb function is very different and I can't navigate on the Morningstar home page to perform the logon function. The Search function works but none of the links including Logon or any link to an article even.

I"m not even sure where to begin to work-around or diagnose this. Anyone had any luck? I'm using Excel 2016 on a Win10 desktop.

Sample links I use to retrieve the financials is:
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=IBM http://financials.morningstar.com/ajax/exportKR2CSV.html?t=IBM

http://financials.morningstar.com/ajax/ReportProcess4CSV.html?reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw&t=IBM http://financials.morningstar.com/ajax/ReportProcess4CSV.html?reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw&t=IBM

Thanks



Fri Jul 7, 2017 11:56 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The last few times I've had to use the Web Query dialog to log in to
various sites, I had to hit the red "X" up on the line with the address bar
several times before I could get the dialog to respond after navigating to
a web page. Otherwise, it seemed like everything was locked up.

But I'm using Office 365 under Win10, both desktop and laptop.

Just checked -- I'm still getting 10 years of data with the two links you
cited, so it isn't an issue with Morningstar not have 10 years available.

On Fri, Jul 7, 2017 at 10:00 PM, mikemcq802@
​...
wrote:

>
> I'm a subscriber to M* and for several years now I have been retrieving 10
> years of financial statements in Excel via SMF (Income, Balance Sheet, Cash
> Flow). Every now and again Excel "forgets" that I am an M* subscriber and
> I had to use the Excel Data/FromWeb function to logon to M* from within
> Excel. No problem.
>
> But, today, I saw I only got 5 years of data which is the default for
> non-subscribers. So I went to logon through Excel. But, Excel's
> Data/FromWeb function is very different and I can't navigate on the
> Morningstar home page to perform the logon function. The Search function
> works but none of the links including Logon or any link to an article even.
>
> I"m not even sure where to begin to work-around or diagnose this. Anyone
> had any luck? I'm using Excel 2016 on a Win10 desktop.
>
> Sample links I use to retrieve the financials is:
>
> http://financials.morningstar.com/ajax/exportKR2CSV.html?t=IBM
>
> http://financials.morningstar.com/ajax/ReportProcess4CSV.
> html?reportType=is&period=12&dataType=A&order=asc&
> columnYear=10&rounding=3&denominatorView=raw&t=IBM
>
>

Sat Jul 8, 2017 8:59 am (PDT) . Posted by:

mikemcq802

I didn't see a "red X" or even an address bar but I have resolved the issue.

In Excel, I first had to go to File / Options / Data and enable the "Show Legacy Data Import" for "From Web (Legacy)"

Then, in the "Data" tab in Excel, choose "Get Data" / "Legacy Wizards" / "From Web (Legacy)"

Entering the url for morningstar brought up its home page like I have been accustomed to and the logon worked fine. I had to exit Excel and restart to make the logon effective (could not just refresh the worksheet containing the morningstar links).

I am not sure when this changed in Excel but its disconcerting to think a needed function is now called "Legacy" and requires special enabling.

Sat Jul 8, 2017 12:55 pm (PDT) . Posted by:

"Mike" mikewa_68

I feel like the odd man out here, so I suspect I'm missing something simple.. 

I've pulled in the latest Beta version of the XLA, and all the APIs work well for me except for the ones that pull info by date.The samples don't work, much less any simple ones I try.. 

When I open the RCHGetYahooHistory or smfPricesByDates samples off the site, open and update them, I get either empty cells, #NAME?, #NA or Error

All the other APIs work without an issue.. 

I have the Add-In located at C:\SMF Add-In
I've got the Add-In installed properly, as other APIs (RCHGetYahooQuotes, RCHGetElementNumber) work without issue.. 

In the samples, where both RCHGetYahooQuotes and smfPricesByDates exist, RCHGetYahooQuotes works great, but smfPricesByDates fails.. 
Any insight?


On Saturday, June 3, 2017 2:07 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:


  Two issues:
1. The new method of getting historical quotes from Yahoo is slower than before, and you are calling the process twice in that function. You should either get all four prices at once and compute the returns, or retrieve the current price with RCHGetYahooQuotes(). Or, if you have a cell with the current price in it, just do:
=M14/smfPricesByDates(A14, N$5:P$5)-1

2. Using NOW() in your function makes it volatile. That means it will recalculate EVERY time you make a change in the workbook. Even entering a simple value in an unrelated cell. Another reason to use RCHGetYahooQuotes() -- to get the current value. If I need the "current" date, I usually use this in a cell so I can refer to it and not make anything volatile:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))

On Sat, Jun 3, 2017 at 6:36 AM, krisha_mehta@​.. wr​ote:

I use smfPricesByDates a lot..
I downloaded the beta version it all works now and is backward compatible. I didn't have to change any code. It is lot slower..I use this formula across an array of about 100 stocks{​​= ​​(smfPricesByDates(A14,NOW()-1) - smfPricesByDates(A14,N$5:P$5)) / smfPricesByDates(A14, N$5:P$5)}

N$5:P$5 has values for 1, 5 and 10 years respectively.
I use this to calculate 1 yr, 5 yr, 10 yr  returns based on Now(). Any other suggestions 

#yiv2536756422 #yiv2536756422 -- #yiv2536756422ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2536756422 #yiv2536756422ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2536756422 #yiv2536756422ygrp-mkp #yiv2536756422hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2536756422 #yiv2536756422ygrp-mkp #yiv2536756422ads {margin-bottom:10px;}#yiv2536756422 #yiv2536756422ygrp-mkp .yiv2536756422ad {padding:0 0;}#yiv2536756422 #yiv2536756422ygrp-mkp .yiv2536756422ad p {margin:0;}#yiv2536756422 #yiv2536756422ygrp-mkp .yiv2536756422ad a {color:#0000ff;text-decoration:none;}#yiv2536756422 #yiv2536756422ygrp-sponsor #yiv2536756422ygrp-lc {font-family:Arial;}#yiv2536756422 #yiv2536756422ygrp-sponsor #yiv2536756422ygrp-lc #yiv2536756422hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2536756422 #yiv2536756422ygrp-sponsor #yiv2536756422ygrp-lc .yiv2536756422ad {margin-bottom:10px;padding:0 0;}#yiv2536756422 #yiv2536756422actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2536756422 #yiv2536756422activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2536756422 #yiv2536756422activity span {font-weight:700;}#yiv2536756422 #yiv2536756422activity span:first-child {text-transform:uppercase;}#yiv2536756422 #yiv2536756422activity span a {color:#5085b6;text-decoration:none;}#yiv2536756422 #yiv2536756422activity span span {color:#ff7900;}#yiv2536756422 #yiv2536756422activity span .yiv2536756422underline {text-decoration:underline;}#yiv2536756422 .yiv2536756422attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2536756422 .yiv2536756422attach div a {text-decoration:none;}#yiv2536756422 .yiv2536756422attach img {border:none;padding-right:5px;}#yiv2536756422 .yiv2536756422attach label {display:block;margin-bottom:5px;}#yiv2536756422 .yiv2536756422attach label a {text-decoration:none;}#yiv2536756422 blockquote {margin:0 0 0 4px;}#yiv2536756422 .yiv2536756422bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2536756422 .yiv2536756422bold a {text-decoration:none;}#yiv2536756422 dd.yiv2536756422last p a {font-family:Verdana;font-weight:700;}#yiv2536756422 dd.yiv2536756422last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2536756422 dd.yiv2536756422last p span.yiv2536756422yshortcuts {margin-right:0;}#yiv2536756422 div.yiv2536756422attach-table div div a {text-decoration:none;}#yiv2536756422 div.yiv2536756422attach-table {width:400px;}#yiv2536756422 div.yiv2536756422file-title a, #yiv2536756422 div.yiv2536756422file-title a:active, #yiv2536756422 div.yiv2536756422file-title a:hover, #yiv2536756422 div.yiv2536756422file-title a:visited {text-decoration:none;}#yiv2536756422 div.yiv2536756422photo-title a, #yiv2536756422 div.yiv2536756422photo-title a:active, #yiv2536756422 div.yiv2536756422photo-title a:hover, #yiv2536756422 div.yiv2536756422photo-title a:visited {text-decoration:none;}#yiv2536756422 div#yiv2536756422ygrp-mlmsg #yiv2536756422ygrp-msg p a span.yiv2536756422yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2536756422 .yiv2536756422green {color:#628c2a;}#yiv2536756422 .yiv2536756422MsoNormal {margin:0 0 0 0;}#yiv2536756422 o {font-size:0;}#yiv2536756422 #yiv2536756422photos div {float:left;width:72px;}#yiv2536756422 #yiv2536756422photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv2536756422 #yiv2536756422photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2536756422 #yiv2536756422reco-category {font-size:77%;}#yiv2536756422 #yiv2536756422reco-desc {font-size:77%;}#yiv2536756422 .yiv2536756422replbq {margin:4px;}#yiv2536756422 #yiv2536756422ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2536756422 #yiv2536756422ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2536756422 #yiv2536756422ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2536756422 #yiv2536756422ygrp-mlmsg select, #yiv2536756422 input, #yiv2536756422 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2536756422 #yiv2536756422ygrp-mlmsg pre, #yiv2536756422 code {font:115% monospace;}#yiv2536756422 #yiv2536756422ygrp-mlmsg * {line-height:1.22em;}#yiv2536756422 #yiv2536756422ygrp-mlmsg #yiv2536756422logo {padding-bottom:10px;}#yiv2536756422 #yiv2536756422ygrp-msg p a {font-family:Verdana;}#yiv2536756422 #yiv2536756422ygrp-msg p#yiv2536756422attach-count span {color:#1E66AE;font-weight:700;}#yiv2536756422 #yiv2536756422ygrp-reco #yiv2536756422reco-head {color:#ff7900;font-weight:700;}#yiv2536756422 #yiv2536756422ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2536756422 #yiv2536756422ygrp-sponsor #yiv2536756422ov li a {font-size:130%;text-decoration:none;}#yiv2536756422 #yiv2536756422ygrp-sponsor #yiv2536756422ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2536756422 #yiv2536756422ygrp-sponsor #yiv2536756422ov ul {margin:0;padding:0 0 0 8px;}#yiv2536756422 #yiv2536756422ygrp-text {font-family:Georgia;}#yiv2536756422 #yiv2536756422ygrp-text p {margin:0 0 1em 0;}#yiv2536756422 #yiv2536756422ygrp-text tt {font-size:120%;}#yiv2536756422 #yiv2536756422ygrp-vital ul li:last-child {border-right:none !important;}#yiv2536756422

Sat Jul 8, 2017 3:14 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

These two examples from the documentation currently work for me:

=smfPricesByDates("IBM", DATE(2007,1,1))
=smfPricesByDates("IBM", "1/1/2007";)

First, check the version of the add-in you're running:

=RCHGetElementNumber("Version&quot;)

-- #NAME? would mean the function is not available.
-- #NA being returned would mean the date isn't valid.
-- "Error" can be any variety of things, as could the blanks.

On Sat, Jul 8, 2017 at 12:51 PM, Mike mikewa_68@
​...
wrote:

>
> I feel like the odd man out here, so I suspect I'm missing something
> simple..
>
> I've pulled in the latest Beta version of the XLA, and all the APIs work
> well for me except for the ones that pull info by date.
> The samples don't work, much less any simple ones I try..
>
> When I open the RCHGetYahooHistory or smfPricesByDates samples off the
> site, open and update them, I get either empty cells, #NAME?, #NA or Error
>
> All the other APIs work without an issue..
>
> I have the Add-In located at C:\SMF Add-In
> I've got the Add-In installed properly, as other APIs (RCHGetYahooQuotes, RCHGetElementNumber)
> work without issue..
>
> In the samples, where both RCHGetYahooQuotes and smfPricesByDates exist,
> RCHGetYahooQuotes works great, but smfPricesByDates fails..
>
> Any insight?
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 19.11

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)