Kamis, 01 Juni 2017

[smf_addin] Digest Number 4046

13 Messages

Digest #4046
1b
Re: smfGetCSVFile function by "Randy Harmelink" rharmelink
4a
Automation Error by lewglenn
4b
Re: Automation Error by "Randy Harmelink" rharmelink
4c
5b
Re: Yahoo Mutual Fund data by "Randy Harmelink" rharmelink
5d
Re: Yahoo Mutual Fund data by "Randy Harmelink" rharmelink

Messages

Wed May 31, 2017 10:34 pm (PDT) . Posted by:

rimanda1

Randy,
I have a spreadsheet that has been working perfectly for some years. The smfGetCSVFile function is now only returning 5 years of data rather than the 10 years requested. Since this function calls RCHGetYahooQuotes i wonder if this is the issue? If I simply paste the URL in my browser I do get the 10 years data returned.


The function in Excel is:
=smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=" & A1 & "&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw")

Thanks in advance
David


I have provided debug details of the code below to assist:


smfGetCSVFile = RCHGetYahooQuotes(pURL, "", pDelimiter:=pDelimiter, pDim1:=pDim1, pDim2:=pDim2)
where purl = http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=aapl&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=aapl&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw



In the called function modGetYahooQuotes, in the line:
sqdata = RCHGetURLData(sURL)
the value of sURL is http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=aapl&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=aapl&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw
The value of sqdata returned from RCHGetURLData(sURL) only shows 5 month history not the 10 months as it should (snippet below).
?sqdata
APPLE INC (AAPL) CashFlowFlag INCOME STATEMENT
Fiscal year ends in September. ,2012-09,2013-09,2014-09,2015-09,2016-09,TTM

Revenue,156508000000,170910000000,182795000000,233715000000,215639000000,220457000000


Wed May 31, 2017 10:45 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

When I cut and pasted your formula, I also only got five years. So I went
to the morningstar.com website using the EXCEL Web Query dialog (alt+d+d+w)
and logged in, creating a security cookie. Then I ran the
smfForceRecalculation macro, and 10 years + TTM appeared.

Try the same and you should see the data.

On Wed, May 31, 2017 at 9:39 PM, rimanda1@
​...
wrote:

> I have a spreadsheet that has been working perfectly for some years. The
> smfGetCSVFile function is now only returning 5 years of data rather than
> the 10 years requested. Since this function calls RCHGetYahooQuotes i
> wonder if this is the issue? If I simply paste the URL in my browser I do
> get the 10 years data returned.
>
> The function in Excel is:
>
> =smfGetCSVFile("http://financials.morningstar.com/
> ajax/ReportProcess4CSV.html?t=" & A1 & "&reportType=is&period=12&
> dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw")
>
>
>

Thu Jun 1, 2017 4:30 am (PDT) . Posted by:

rimanda1

Really interesting - I always have used the WEB query dialog to login (because of the cookie issue). I followed your advice and ensured I was logged in (while still in the WEB dialog) by recalling my watchlist. I then closed the dialog and low and behold back came 10 years!

I guess the new MS WEB site has some quirks and I need to actually change pages after logging in...


Anyway at least I have a "workaround" - As I am sure you do, I wish companies wouldn't radically change their WEB site
Thanks for your as always prompt response.
DG

Thu Jun 1, 2017 8:49 am (PDT) . Posted by:

eadamy

Don't hold your breath. I reported 7 symbols on May 19th. They are all ranked #1489 and the data is still bad. 4 have a null on 6/29/16 which is readily handled by carrying the 28th forward. 3 are so full of nulls that they are unusable. I ended up building my own close adjusted data.

Earl

Thu Jun 1, 2017 10:03 am (PDT) . Posted by:

lexstar

Does smfPricesBetween() work anyone today? My returns only 0.

EXAMPLE: =TRANSPOSE(smfPricesBetween("SPY",5/1/2017,5/31/2017,"08020406")

Thu Jun 1, 2017 10:35 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

That's because of your dates. 5/1/2017 is 5 divided by 1 divided by 2017.
That function requires EXCEL serial date values, as in:

=TRANSPOSE(smfPricesBetween("SPY",DATE(2017,5,1),DATE(2017,5,31),"08020406"))

On Thu, Jun 1, 2017 at 10:03 AM, lexstar@
​...
wrote:

>
> Does smfPricesBetween() work anyone today? My returns only 0.
>
> EXAMPLE: =TRANSPOSE(smfPricesBetween("SPY",5/1/2017,5/31/2017,"08020406")
>

Thu Jun 1, 2017 11:00 am (PDT) . Posted by:

lewglenn

I've been running vba macros for some time with calls to RCHGetYahooQuotes and now also smfGetYahooHistory, without any problem. Normally I run the macros directly from the EXCEL Quick Access Toolbar, having customized the Macro command to do so. Lately, when I do this I get the message:

Automation Error. Exception Occurred.

and Windows closes EXCEL. However, when I go directly to vba (by choosing alt-F11) and then running the macro, it works just fine.


Any suggestions as to what is happening and how to correct this problem?

Thu Jun 1, 2017 12:06 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I've not seen that error. I did a Google search on the message. This
sounded similar to your symptoms:

https://stackoverflow.com/questions/36779550/automation-error-exception-occurred-when-running-a-macro

A link there:

https://stackoverflow.com/questions/36728143/why-do-i-need-to-have-the-vba-editor-window-open-for-my-macro-to-work

...seems to indicate some type of memory problem. That would make sense if
you're doing a lot of smfGetYahooHistory() functions? As I mentioned, the
new web pages are HUGE, so they'll use up a lot more memory in the VBA
environment.

Sites that sell "registry fix" programs claim that "95% of the time" it's
from a registry problem. But they may be heavily biased...

On Thu, Jun 1, 2017 at 11:00 AM, lewglenn@
​...
wrote:

>
> I've been running vba macros for some time with calls to
> RCHGetYahooQuotes and now also smfGetYahooHistory, without any problem.
> Normally I run the macros directly from the EXCEL Quick Access Toolbar,
> having customized the Macro command to do so. Lately, when I do this I get
> the message:
>
> Automation Error. Exception Occurred.
>
> and Windows closes EXCEL. However, when I go directly to vba (by choosing
> alt-F11) and then running the macro, it works just fine.
>
> Any suggestions as to what is happening and how to correct this problem?
>

Thu Jun 1, 2017 4:29 pm (PDT) . Posted by:

lewglenn

The remarkable thing is that when I run the same code on my laptop it runs just fine from the EXCEL QuickAccess Toolbar.

The fix for my desktop that was giving me the problem was, as the link you sent suggested, to simply add the following 2 lines to the beginning of my macros:
Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.Visible = False

This worked like a charm.

Thanks again for your help, Randy.

Thu Jun 1, 2017 2:35 pm (PDT) . Posted by:

rus101

Hi Randy (and rest of you guys),

Thanks for working on fixing the Yahoo Price History issues, that's been very helpful.

Any thoughts on other Yahoo items, particularly mutual fund / ETF risk metrics. I have some spreadsheet that pull in St.Deviation and other metrics and it's been busted since Yahoo changeover. Those are SMF items 5093-5139.

Is there any way to extract the data on new pages, such as

https://finance.yahoo.com/quote/VFINX/risk?p=VFINX https://finance.yahoo.com/quote/VFINX/risk?p=VFINX

Thanks for all your help!
Denis




Thu Jun 1, 2017 3:10 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

After much trial and error, the current status is this database, which I
uploaded last month, and may change:

http://ogres-crypt.com/SMF/Elements/smfGetYahooJSONField-Mutual-Fund-Keyname-Database.xls

For example, at the top of the file is something like:

*Module* *Usable field name*
assetProfile quoteSummary.result.0.assetProfile.address1
assetProfile quoteSummary.result.0.assetProfile.phone
assetProfile quoteSummary.result.0.assetProfile.longBusinessSummary
assetProfile quoteSummary.result.0.assetProfile.maxAge

The module and field name are used in a new function. For example, this:

=smfGetYahooJSONField("VFINX","assetProfile","quoteSummary.result.0.assetProfile.longBusinessSummary")

...returns:

*"The investment seeks to track the performance of a benchmark index that
measures the investment return of large-capitalization stocks. The fund
employs an indexing investment approach designed to track the performance
of the Standard & Poor's 500 Index, a widely recognized benchmark of
U.S. stock market performance that is dominated by the stocks of large U.S.
companies. The advisor attempts to replicate the target index by investing
all, or substantially all, of its assets in the stocks that make up the
index, holding each stock in approximately the same proportion as its
weighting in the index."*

Between the modules I've found, there are over 1000 field names (most on
the performance module). Stocks have over 3000 field names (so far) on
their modules.

I got dragged off working on that stuff by the big change in historical
quotes. One point of coming up with the new function is so that it could be
used in element definitions.

On Thu, Jun 1, 2017 at 2:35 PM, rus101@
​...
wrote:

>
> Hi Randy (and rest of you guys),
>
> Thanks for working on fixing the Yahoo Price History issues, that's been
> very helpful.
>
> Any thoughts on other Yahoo items, particularly mutual fund / ETF risk
> metrics. I have some spreadsheet that pull in St.Deviation and other
> metrics and it's been busted since Yahoo changeover. Those are SMF items
> 5093-5139.
>
> Is there any way to extract the data on new pages, such as
>
> https://finance.yahoo.com/quote/VFINX/risk?p=VFINX
>
>

Thu Jun 1, 2017 4:22 pm (PDT) . Posted by:

rus101

That's really cool, thanks Randy!

I used the database to re-create some of my old data items in the JSON format. I uploaded a template to "Uploads by forum members" under "Yahoo JSON Mutual Fund data points.xlsx" if anyone else is interested in using the new format to pull in MF data.

Thu Jun 1, 2017 4:38 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I note you have a few ETFs in your workbook. The mutual fund field names
aren't necessarily going to work with ETFs. I think MOST do, but there will
be discrepancies. The rating is an obvious one.

I also found it disconcerting that "0.stdDev.raw" is for 5-year, while
"1.stdDev.raw" is for 3-year, and "2.stdDev.raw" is for 10-year. I do
wonder if they will stay in that order. I would have ordered them 3/5/10.

On Thu, Jun 1, 2017 at 4:22 PM, rus101@
​...
wrote:

>
> That's really cool, thanks Randy!
>
> I used the database to re-create some of my old data items in the JSON
> format. I uploaded a template to "Uploads by forum members" under "Yahoo
> JSON Mutual Fund data points.xlsx" if anyone else is interested in using
> the new format to pull in MF data.
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 22.49

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)