15 Messages
Digest #4277
5b
Re: morningstar smfGetCSVfile difficulties with company report data by "Randy Harmelink" rharmelink
Messages
Wed Dec 27, 2017 7:35 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The problem is that the data isn't in an HTML table, so there is no "table
cell" to pull the data out of. They use "div" tags to create a table
format. You'd need to extract is with something like:
=smfGetTagContent("http://www.nasdaq.com/symbol/MMM","div",1,"1 Year
Target",,,,1)
You do need a bookmarkable URL in order for the add-in to extract data
from, because it pulls data out of the source code of a web page. It
doesn't interact with a browser object that constructs a presentable web
page.
Having said that, just because you can't SEE the data in your browser
without clicking on something doesn't necessarily mean it isn't within the
source code of the web page someplace. SOME web pages have a hide/display
toggle, so when you "click" on a tab, it just hides the tab you're looking
at and displays the data for the tab you clicked on. I often have to review
the source code of the web page to see what is going on, and if the data is
actually there, and how to extract it based on how they coded the web page.
You can get the current Consensus Price Target from Marketbeat.com with
something like:
=RCHGetTableCell("https://www.marketbeat.com/stocks/NYSE/MMM/",1,">Consensus
Price Target")
On Wed, Dec 27, 2017 at 6:39 PM, rogacox@
...
wrote:
>
> Hi Randy, I am running into the same problem of not being able to grab
> data from nasdaq. For instance when I use the line code
> of RCHGetTableCell("www.nasdaq.com/symbol/~~~~~",1,"1 Year Target") I
> get an error message. I have tried variants replacing the "~~~~~" with a
> cell reference and get a #VALUE! error message. If I could grab a string
> from the web page and examine it, I could extract the numbers I need from
> the string. Any help is much appreciated. Also, some sited like
> Marketbeat.com display a stock's information using tabs all within the same
> url. I am trying to extract target price information - in this case from
> the Analyst Ratings tab. Since the url does not change, how do I access
> the information.
>
cell" to pull the data out of. They use "div" tags to create a table
format. You'd need to extract is with something like:
=smfGetTagContent("http://www.nasdaq.com/symbol/MMM","div",1,"1 Year
Target",,,,1)
You do need a bookmarkable URL in order for the add-in to extract data
from, because it pulls data out of the source code of a web page. It
doesn't interact with a browser object that constructs a presentable web
page.
Having said that, just because you can't SEE the data in your browser
without clicking on something doesn't necessarily mean it isn't within the
source code of the web page someplace. SOME web pages have a hide/display
toggle, so when you "click" on a tab, it just hides the tab you're looking
at and displays the data for the tab you clicked on. I often have to review
the source code of the web page to see what is going on, and if the data is
actually there, and how to extract it based on how they coded the web page.
You can get the current Consensus Price Target from Marketbeat.com with
something like:
=RCHGetTableCell("https://www.marketbeat.com/stocks/NYSE/MMM/",1,"
Price Target"
On Wed, Dec 27, 2017 at 6:39 PM, rogacox@
...
wrote:
>
> Hi Randy, I am running into the same problem of not being able to grab
> data from nasdaq. For instance when I use the line code
> of RCHGetTableCell(
> get an error message. I have tried variants replacing the "~~~~~" with a
> cell reference and get a #VALUE! error message. If I could grab a string
> from the web page and examine it, I could extract the numbers I need from
> the string. Any help is much appreciated. Also, some sited like
> Marketbeat.com display a stock's information using tabs all within the same
> url. I am trying to extract target price information - in this case from
> the Analyst Ratings tab. Since the url does not change, how do I access
> the information.
>
Thu Dec 28, 2017 8:30 am (PST) . Posted by:
rogacox
Thanks, Randy. Works like a charm. Out of curiosity, I have been trying to combine 2 spreadsheets - one using the smfGetPortfolioView function and the other using the other older functions (zacks or morningstar with an index number) and other columns with the Get functions (WebData and CellTable, etc.) While the spreadsheets work as separate tabs in a worksheet, when I combine the spreadsheets, the second one which is positionally to the right of the first using the same column of ticker symbols appears not to work. I have separated the two sets of computational matrices by 3 columns marked with the X function to ignore some columns of manual calculation. Is there a trick to getting the 2 kinds of functions to work together?
Thanks again for all your help.
Roger
Thanks again for all your help.
Roger
Thu Dec 28, 2017 9:02 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm very confused by your description.
There should be no reason the formulas shouldn't work together. And I have
no idea what the "X function" is. I might guess it was for ignoring columns
when using the smfUpdateDownloadTable macro, but smfGetYahooPortfolioView()
should never be used with that macro.
On Thu, Dec 28, 2017 at 9:30 AM, rogacox@
...
wrote:
>
> Thanks, Randy. Works like a charm. Out of curiosity, I have been trying
> to combine 2 spreadsheets - one using the smfGetPortfolioView function and
> the other using the other older functions (zacks or morningstar with an
> index number) and other columns with the Get functions (WebData and
> CellTable, etc.) While the spreadsheets work as separate tabs in a
> worksheet, when I combine the spreadsheets, the second one which is
> positionally to the right of the first using the same column of ticker
> symbols appears not to work. I have separated the two sets of
> computational matrices by 3 columns marked with the X function to ignore
> some columns of manual calculation. Is there a trick to getting the 2
> kinds of functions to work together?
>
>
There should be no reason the formulas shouldn'
no idea what the "X function" is. I might guess it was for ignoring columns
when using the smfUpdateDownloadTa
should never be used with that macro.
On Thu, Dec 28, 2017 at 9:30 AM, rogacox@
...
wrote:
>
> Thanks, Randy. Works like a charm. Out of curiosity, I have been trying
> to combine 2 spreadsheets - one using the smfGetPortfolioView function and
> the other using the other older functions (zacks or morningstar with an
> index number) and other columns with the Get functions (WebData and
> CellTable, etc.) While the spreadsheets work as separate tabs in a
> worksheet, when I combine the spreadsheets, the second one which is
> positionally to the right of the first using the same column of ticker
> symbols appears not to work. I have separated the two sets of
> computational matrices by 3 columns marked with the X function to ignore
> some columns of manual calculation. Is there a trick to getting the 2
> kinds of functions to work together?
>
>
Thu Dec 28, 2017 9:20 am (PST) . Posted by:
rogacox
Thanks. Good guess on the "X function." I'm sure that is what is gumming up the works. I can still combine the spreadsheets in second spreadsheet if I use standard cell reference commands from the first spreadsheet (PortfolioView) to the second spreadsheet (UpdateDownloadTable) if I position the data to the left of the ticker symbol column. I appreciate all your help. Roger
Thu Dec 28, 2017 9:38 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The smfUpdateDownloadTable macro keys off a defined name of "Ticker". If
that isn't copied or placed correctly, the macro won't work on a new
worksheet.
Sorry, but I'm still having problem visualizing what you are doing...
On Thu, Dec 28, 2017 at 10:20 AM, rogacox@
...
wrote:
>
> Thanks. Good guess on the "X function." I'm sure that is what is gumming
> up the works. I can still combine the spreadsheets in second spreadsheet
> if I use standard cell reference commands from the first spreadsheet
> (PortfolioView) to the second spreadsheet (UpdateDownloadTable) if I
> position the data to the left of the ticker symbol column. I appreciate
> all your help.
>
>
that isn't copied or placed correctly, the macro won't work on a new
worksheet.
Sorry, but I'm still having problem visualizing what you are doing...
On Thu, Dec 28, 2017 at 10:20 AM, rogacox@
...
wrote:
>
> Thanks. Good guess on the "X function." I'm sure that is what is gumming
> up the works. I can still combine the spreadsheets in second spreadsheet
> if I use standard cell reference commands from the first spreadsheet
> (PortfolioView) to the second spreadsheet (UpdateDownloadTabl
> position the data to the left of the ticker symbol column. I appreciate
> all your help.
>
>
Wed Dec 27, 2017 8:08 pm (PST) . Posted by:
"Craig Passow" passow
You *can* get unadjusted closing prices, so that allows you to plot
position value as a function of date for a specified number of shares.
In order to properly reflect the impact that splits and dividends have
on your total return using unadjusted prices, you will have to change
the number of shares to reflect any splits and add in (or reinvest) any
dividends received.
The fact that you're looking at short time periods makes it less likely
you will encounter a dividend or a split, but your results will be wrong
if you ignore them. To see this, take a look at the unadjusted price
chart of pretty much any mutual fund over the past month - most of them
have a discontinuity resulting from year-end distributions which make
all those charts useless (example: FCNTX
<https://finance.yahoo.com/quote/FCNTX/chart?p=FCNTX>). Depending on how
accurate your record keeping is, you might get a similar result for less
effort by using the adjusted prices. Note that if your buy/sell prices
are intra-day (not closing prices), you still have to adjust them if
using adjusted prices for the rest of the dates.
On 12/27/2017 7:01 PM,
v4pnaah6cygf2nzx2356pl2ugoa3ti5fowi4gdzj@yahoo.com [smf_addin] wrote:
>
>
> Thanks for your reply.
> What I wanted to do was tabulate and plot portfolio performance Vs.
> date (daily closing price) taking into account dividends separately.
> I am only considering a short time period, so didn't want to include
> splits and/or dividends.
>
>
> I guess what I can do is take the difference between my purchase price
> and the adjusted closing prices. This would give me performance
> including dividends and any splits.
>
> Thanks for your insight.
>
> Ron Johnson
>
position value as a function of date for a specified number of shares.
In order to properly reflect the impact that splits and dividends have
on your total return using unadjusted prices, you will have to change
the number of shares to reflect any splits and add in (or reinvest) any
dividends received.
The fact that you're looking at short time periods makes it less likely
you will encounter a dividend or a split, but your results will be wrong
if you ignore them. To see this, take a look at the unadjusted price
chart of pretty much any mutual fund over the past month - most of them
have a discontinuity resulting from year-end distributions which make
all those charts useless (example: FCNTX
<https://finance.yahoo.com/quote/FCNTX/chart?p=FCNTX>). Depending on how
accurate your record keeping is, you might get a similar result for less
effort by using the adjusted prices. Note that if your buy/sell prices
are intra-day (not closing prices), you still have to adjust them if
using adjusted prices for the rest of the dates.
On 12/27/2017 7:01 PM,
v4pnaah6cygf2nzx2356pl2ugoa3ti5fowi4gdzj@yahoo.com [smf_addin] wrote:
>
>
> Thanks for your reply.
> What I wanted to do was tabulate and plot portfolio performance Vs.
> date (daily closing price) taking into account dividends separately.
> I am only considering a short time period, so didn't want to include
> splits and/or dividends.
>
>
> I guess what I can do is take the difference between my purchase price
> and the adjusted closing prices. This would give me performance
> including dividends and any splits.
>
> Thanks for your insight.
>
> Ron Johnson
>
Wed Dec 27, 2017 8:28 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
A big issue right now is that Yahoo apparently isn't posting dividends to
the historical data (or adjusting the quotes).
I just had a question on another board about FOOLX -- a $25 fund that just
paid out $3.10 in distributions. So it appears as a 12% drop in a day, even
though it actually had a gain that day!
On Wed, Dec 27, 2017 at 9:08 PM, Craig Passow passow@
...
wrote:
> You *can* get unadjusted closing prices, so that allows you to plot
> position value as a function of date for a specified number of shares. In
> order to properly reflect the impact that splits and dividends have on your
> total return using unadjusted prices, you will have to change the number of
> shares to reflect any splits and add in (or reinvest) any dividends
> received.
>
> The fact that you're looking at short time periods makes it less likely
> you will encounter a dividend or a split, but your results will be wrong if
> you ignore them. To see this, take a look at the unadjusted price chart of
> pretty much any mutual fund over the past month - most of them have a
> discontinuity resulting from year-end distributions which make all those
> charts useless (example: FCNTX
> <https://finance.yahoo.com/quote/FCNTX/chart?p=FCNTX>). Depending on how
> accurate your record keeping is, you might get a similar result for less
> effort by using the adjusted prices. Note that if your buy/sell prices are
> intra-day (not closing prices), you still have to adjust them if using
> adjusted prices for the rest of the dates.
>
the historical data (or adjusting the quotes).
I just had a question on another board about FOOLX -- a $25 fund that just
paid out $3.10 in distributions. So it appears as a 12% drop in a day, even
though it actually had a gain that day!
On Wed, Dec 27, 2017 at 9:08 PM, Craig Passow passow@
...
wrote:
> You *can* get unadjusted closing prices, so that allows you to plot
> position value as a function of date for a specified number of shares. In
> order to properly reflect the impact that splits and dividends have on your
> total return using unadjusted prices, you will have to change the number of
> shares to reflect any splits and add in (or reinvest) any dividends
> received.
>
> The fact that you're looking at short time periods makes it less likely
> you will encounter a dividend or a split, but your results will be wrong if
> you ignore them. To see this, take a look at the unadjusted price chart of
> pretty much any mutual fund over the past month - most of them have a
> discontinuity resulting from year-end distributions which make all those
> charts useless (example: FCNTX
> <https://finance.yahoo.com/quote/FCNTX/chart?p=FCNTX>). Depending on how
> accurate your record keeping is, you might get a similar result for less
> effort by using the adjusted prices. Note that if your buy/sell prices are
> intra-day (not closing prices), you still have to adjust them if using
> adjusted prices for the rest of the dates.
>
Thu Dec 28, 2017 1:53 pm (PST) . Posted by:
kitiany
I was trying to get info from a page like this one, http://www..sectorspdr.com/sectorspdr/sector/xlk/holdings http://www.sectorspdr.com/sectorspdr/sector/xlk/holdings, but got nowhere.
Randy, any suggestions.
Thanks,
Kit
Randy, any suggestions.
Thanks,
Kit
Thu Dec 28, 2017 3:57 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Unfortunately, the add-in needs that data to be within the source code of
the web page, and this web page is built dynamically using data retrieved
by APIs that can only be accessed via their own domain. However, the CSV
export file appears to be available. Try something like:
=smfGetCSVFile("
http://www.sectorspdr.com/sectorspdr/IDCO.Client.Spdrs.Holdings/Export/ExportCsv?symbol=xlk
")
On Thu, Dec 28, 2017 at 2:53 PM, Kitiany@
...
wrote:
> I was trying to get info from a page like this one,
> http://www.sectorspdr.com/sectorspdr/sector/xlk/holdings, but got nowhere.
>
>
>
the web page, and this web page is built dynamically using data retrieved
by APIs that can only be accessed via their own domain. However, the CSV
export file appears to be available. Try something like:
=smfGetCSVFile("
http://www.sectorspdr.com/sectorspdr/IDCO.Client.Spdrs.Holdings/Export/ExportCsv?symbol=xlk
")
On Thu, Dec 28, 2017 at 2:53 PM, Kitiany@
...
wrote:
> I was trying to get info from a page like this one,
> http://www.sectorspdr.com/sectorspdr/sector/xlk/holdings, but got nowhere.
>
>
>
Thu Dec 28, 2017 3:37 pm (PST) . Posted by:
"erin wallace" pigletmc
Hi Randy,
Can you help me out with the attached file? I can't get the yellowed cells to populate.
Thanks,Mark
| | Virus-free. www.avast.com |
Can you help me out with the attached file? I can't get the yellowed cells to populate.
Thanks,Mark
| | Virus-free. www.avast.com |
Thu Dec 28, 2017 4:19 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Interesting. The problem appears to be that you can't use an upper case
ticker symbol in the URL. That is, this:
=RCHGetWebData("https://www.benzinga.com/stock/biib")
....returns the source code of a web page, while this:
=RCHGetWebData("https://www.benzinga.com/stock/BIIB")
....does not. The browser redirects the latter URL to be the former, but
apparently Microsoft's XMLHTTP routine is not handling the redirection
gracefully.
Easiest fix it to use EXCEL's LOWER() function when creating the URL. For
example, if I use this in cell D12, it works:
=MAX(RCHGetTableCell("https://www.benzinga.com/stock/
"&LOWER(D11)&"",5,">DATE",,,,3,"</table",,"--"),RCHGetTableCell("
https://www.benzinga.com/stock/
"&LOWER(D11)&"",5,">DATE",,,,2,"</table",,"--"),RCHGetTableCell("
https://www.benzinga.com/stock/
"&LOWER(D11)&"",5,">DATE",,,,1,"</table",,"--"))
On Thu, Dec 28, 2017 at 1:25 PM, erin wallace pigletmc@... wrote:
>
> Can you help me out with the attached file? I can't get the yellowed
> cells to populate.
>
>
>
ticker symbol in the URL. That is, this:
=RCHGetWebData("https://www.benzinga.com/stock/biib")
....returns the source code of a web page, while this:
=RCHGetWebData("https://www.benzinga.com/stock/BIIB")
....does not. The browser redirects the latter URL to be the former, but
apparently Microsoft's XMLHTTP routine is not handling the redirection
gracefully.
Easiest fix it to use EXCEL's LOWER() function when creating the URL. For
example, if I use this in cell D12, it works:
=MAX(RCHGetTableCell("https://www.benzinga.com/stock/
"&LOWER(D11)&"",5,">DATE",,,,3,"</table",,"--"),RCHGetTableCell("
https://www.benzinga.com/stock/
"&LOWER(D11)&"",5,">DATE",,,,2,"</table",,"--"),RCHGetTableCell("
https://www.benzinga.com/stock/
"&LOWER(
On Thu, Dec 28, 2017 at 1:25 PM, erin wallace pigletmc@... wrote:
>
> Can you help me out with the attached file? I can't get the yellowed
> cells to populate.
>
>
>
Thu Dec 28, 2017 9:09 pm (PST) . Posted by:
mff2805
Hi,
i've used an adaptation of the subject file for quite a while now. but recently, info is not being returns
the funciton call looks like this
=smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t="&E2&"&reportType="&E3&"&period="&E4&"&dataType="&E5&"&order="&E6&"&columnYear="&E7&"&rounding=3&denominatorView="&E8)
where the cell references refer to ticker, report type, period, etc.
did something change that you are aware of? i hope this capability is not permanently broken.
thanks, mike
i've used an adaptation of the subject file for quite a while now. but recently, info is not being returns
the funciton call looks like this
=smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t="&E2&"
where the cell references refer to ticker, report type, period, etc.
did something change that you are aware of? i hope this capability is not permanently broken.
thanks, mike
Thu Dec 28, 2017 9:24 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
These examples from the message on the group appear to be working fine here:
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG")
=smfGetCSVFile("
http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw
")
On Thu, Dec 28, 2017 at 10:09 PM, mff3429@
...
wrote:
>
> i've used an adaptation of the subject file for quite a while now. but
> recently, info is not being returns
> the funciton call looks like this
> =smfGetCSVFile("http://financials.morningstar.com/
> ajax/ReportProcess4CSV.html?t="&E2&"&reportType="&E3&"&
> period="&E4&"&dataType="&E5&"&order="&E6&"&columnYear="&E7&"
> &rounding=3&denominatorView="&E8)
> where the cell references refer to ticker, report type, period, etc.
>
> did something change that you are aware of? i hope this capability is not
> permanently broken.
>
>
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG")
=smfGetCSVFile("
http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw
")
On Thu, Dec 28, 2017 at 10:09 PM, mff3429@
...
wrote:
>
> i've used an adaptation of the subject file for quite a while now. but
> recently, info is not being returns
> the funciton call looks like this
> =smfGetCSVFile("http://financials.morningstar.com/
> ajax/ReportProcess4
> period="
> &rounding=3&denomin
> where the cell references refer to ticker, report type, period, etc.
>
> did something change that you are aware of? i hope this capability is not
> permanently broken.
>
>
Thu Dec 28, 2017 9:47 pm (PST) . Posted by:
mff2805
ok.
puzzling
when i array-entered your formulas on a blank sheet, they worked fine. so i went to my version and noted that i was not getting the annual income statement, but was getting the other ones - quarterly, balance sheet, etc. so in the annual income statement pull-down options, per the template, i changed one item and then the annual statement showed up. when i changed that pull-down choice back to the original selection, it still worked. I'm not sure what changed, but i'll try this again if i have more difficulties.
this is one of my go-to spreadsheets for examining a companies financial info. i made a collection of charts for all the info from all three report statements and it lets me see at a glance some of the company details.
i just wish there was a means of getting non-GAAP reported info, as that's what analysts seem to more closely watch.
thanks much.
Mike
puzzling
when i array-entered your formulas on a blank sheet, they worked fine. so i went to my version and noted that i was not getting the annual income statement, but was getting the other ones - quarterly, balance sheet, etc. so in the annual income statement pull-down options, per the template, i changed one item and then the annual statement showed up. when i changed that pull-down choice back to the original selection, it still worked. I'm not sure what changed, but i'll try this again if i have more difficulties.
this is one of my go-to spreadsheets for examining a companies financial info. i made a collection of charts for all the info from all three report statements and it lets me see at a glance some of the company details.
i just wish there was a means of getting non-GAAP reported info, as that's what analysts seem to more closely watch.
thanks much.
Mike
Thu Dec 28, 2017 9:57 pm (PST) . Posted by:
mff2805
added note. now when i change the ticker on my master page, i still have to go into each of the downloads and change "as reported" to "restated"; in order to have the income statement show up. when i switch back to "as reported", the data stays in place. rather weird. would you be willing to try the file on your end?
Mike
Mike
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar