Messages
Mon Mar 26, 2018 11:38 am (PDT) . Posted by:
I might have just figured out a crude, but effective way that meets my needs. I am pulling back earnings date from the Barchart View and in there, it appears that, in general, an ETF will have an earnings date of 1/0/1900. While not bulletproof, it is probably more than good enough for what I am trying to do.
To be clear, I would not say this is 100% effective. I'm looking at the list of items and there are some OTC stocks that return that date. Examples:
MDRPF FFMGF
But, I believe these are both incorporated in Canada and trade primarily on the TSX (or maybe Vancouver), so maybe the reporting requirements are different, or maybe it is because they trade OTC. EIther way, just want to make sure that if anyone tries the same, know it is not 100% guaranteed to meet your needs. However, I think this will work for me just fine.
Thanks!
Mon Mar 26, 2018 5:37 pm (PDT) . Posted by:
Field #13 for the smfGetYahooPortfolioView() function is "Quote Type".
Values I've seen:
*Quote Type*
CURRENCY
EQUITY
ETF
FUTURE
MUTUALFUND
OPTION
....but there may be others.
On Mon, Mar 26, 2018 at 11:17 AM, brad.reel@
...
wrote:
> Hi everyone. I have a list of symbols I pull in from a screen. The
> screen is a mish-mash of equities and ETF's. I don't have any way within
> the screen to determine whether a symbol is an equity or ETF. However, it
> would make some of my downstream activities much easier if I could add a
> filter for this.
>
> Does anyone know of an easy way to look up a symbol and determine if it is
> an ETF? Any help appreciated.
>
>
>
Tue Mar 27, 2018 5:37 am (PDT) . Posted by:
Thanks, Randy. I will check it out!
On Mon, Mar 26, 2018 at 7:37 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Field #13 for the smfGetYahooPortfolioView() function is "Quote Type".
> Values I've seen:
>
> *Quote Type*
> CURRENCY
> EQUITY
> ETF
> FUTURE
> MUTUALFUND
> OPTION
> ...but there may be others.
>
> On Mon, Mar 26, 2018 at 11:17 AM, brad.reel@
> ...
> wrote:
>
>> Hi everyone. I have a list of symbols I pull in from a screen. The
>> screen is a mish-mash of equities and ETF's. I don't have any way within
>> the screen to determine whether a symbol is an equity or ETF. However, it
>> would make some of my downstream activities much easier if I could add a
>> filter for this.
>>
>> Does anyone know of an easy way to look up a symbol and determine if it
>> is an ETF? Any help appreciated.
>>
>>
>>
>
Mon Mar 26, 2018 4:03 pm (PDT) . Posted by:
I have about 1000 companies that I get limited annual BS and IS Data on that are currently setup from Google. I have about 180 companies (primarily international) that are from Yahoo. I only need year end annual numbers for: Fiscal Year End Date
Revenue
Cost of Goods
Net Income
Inventories
Assets
Equity
The Inventory I do not believe is in the system for Yahoo. Guessing they don't supply. Not a big deal. If I recall in addition to that Google carried more precision. I also seem to have had more errors from Yahoo.. I would not get number but 3 hours later it would.
Is there another source I should look to?
Mon Mar 26, 2018 5:59 pm (PDT) . Posted by:
Take a look at GuruFocus. It's my current source of historical financial
data, but I'm a paying subscriber now so not really aware of the
limitations for a non-subscriber. But I think they have more annual and
quarterly periods for non-subscribers than you would see with Google or
Yahoo. But, as a subscriber, I can grab a CSV file with 30 years of annual
and quarterly periods.
Another option would be Morningstar. Subscribers get 10 years + TTM and
non-subscribers are limited to 5 years + TTM. See the "FAQs and Tips" page
on the web site for details on grabbing their CSV file.
Another option would be AdvFN, but I haven't used them for a few years. I
moved to GuruFocus because I was getting disappointed by the AdvFN data.
The site was being changed many times, and not for the better. For a while,
their new beta site was looking good, but they abandoned it. Maybe.
I think the new Yahoo JSON files may have whatever data you need. The
available module names and field names should be in the documentation on
the "Elements"; page of the web site. For example:
=smfGetYahooJSONField("MMM","balanceSheetHistory","quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.0.inventory.raw")
Last year was a busy year for web site changes. I still have a long way to
go to get caught up with them all. And some are still in the midst of
changing. :(
On Mon, Mar 26, 2018 at 12:04 PM, carlmims@
...
wrote:
>
> I have about 1000 companies that I get limited annual BS and IS Data on
> that are currently setup from Google. I have about 180 companies
> (primarily international) that are from Yahoo. I only need year end annual
> numbers for:
> Fiscal Year End Date
> Revenue
> Cost of Goods
> Net Income
> Inventories
> Assets
> Equity
>
> The Inventory I do not believe is in the system for Yahoo. Guessing they
> don't supply. Not a big deal. If I recall in addition to that Google
> carried more precision. I also seem to have had more errors from Yahoo.. I
> would not get number but 3 hours later it would.
>
> Is there another source I should look to?
>
>
Mon Mar 26, 2018 5:30 pm (PDT) . Posted by:
The new smfGetYahooPortfolioView() function should be doing real-time
equity quotes and delayed option quotes. Template and documentation are on
the web site.
On Mon, Mar 26, 2018 at 9:26 AM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Hi Randy .... I'm using your addin using Google Finance and
> Googlesheets, to import nearly real time OHLC quotes into Excel per your
> blog guidance from months ago. (And its working)
>
>
>
> I'm wondering if there is still any kind of Yahoo "API" style
> alternative? Such as involving perhaps Portfolio View instead, as I'm not
> familiar with what that does.
>
>
>
> The reason I'm asking ... aside from seeking a backup ... is that I use an
> after hours service for daily updates and the Google Finance data are
> horribly off on the volumes reported intraday.
>
> And .. there are more than a few symbols for which Google Finance seems
> unwilling to furnish quotes. OIH, as one example. OILD and OILU as
> others. There are plenty.
>
>
>
> If there is an Yahoo alternative, please point me in the right
> direction. Thanks again for your continuing efforts.
>
>
>
Tue Mar 27, 2018 2:41 pm (PDT) . Posted by:
Anybody have a good quick way to calculate EV/EBITDA and FCF per Share? I used to have Google Finance balance sheet info feeding into excel and then had a formula to do the cal but obviously no more...
Tue Mar 27, 2018 3:28 pm (PDT) . Posted by:
GuruFocus?
https://www.gurufocus.com/stock/mmm
On Tue, Mar 27, 2018 at 2:41 PM, farris_justin@
...
wrote:
> Anybody have a good quick way to calculate EV/EBITDA and FCF per Share? I
> used to have Google Finance balance sheet info feeding into excel and then
> had a formula to do the cal but obviously no more...
>
Tue Mar 27, 2018 6:39 pm (PDT) . Posted by:
Thanks! I spent some time this evening running through the template on Guru and it has what i need. Now onto rebuilding my sheets
Wed Mar 28, 2018 7:52 am (PDT) . Posted by:
But it is working, since you get the Version and the ConvertData cells to have data.
What exactly is 'nothing works' that you are trying?
On Monday, March 26, 2018, 12:17:21 AM GMT+2, raig002@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Hi Randy:
I installed Office 2016 and now nothing works, I installed the SMF Add-in according to the instructions and I did some troubleshooting, for example, I did the "Fix Links", I re-loaded the add-in, Browsing again and picking the RCH_Stock_Market_Functions.xla file.
I executed the =RCHGetElementNumber("Version") and I get:
| Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (64-bit) NT :.00; 16.0; ; ; 1) |
Also I execute =smfConvertData("1B") and I get : 1000000
So far, nothing works
So, I am wondering What is wrong? It looks like the Add-in is not active, How Can I active it?
Thanks, a lot.
Tony
Thu Mar 29, 2018 1:17 pm (PDT) . Posted by:
Hi Randy .... Happy Holy Thursday, Good Friday and Easter weekend.
Two issues. One installation related. The other pertains to after hours use.
Hopefully, issues are minor.
Installed SMF folder in C drive of new machine, and pointed to the XLA file..
Added the cell with
=RCHGetElementNumber("Version")
It reports back as it should. Not # Name ?
After doing the formula as ArrayEnter,
all the cells appear to have the correct formula with { } just as in the other setup on old machine that works fine.
But I can't get the array to be populated with quotes (simple OHCL vol symbol).
Instead, all the cells in the array have double dashes.
Tried both Update Selection and Update Worksheet.
Second issue ...
on the setup on my existing machine that works, the SMF entry on the drop down menu pointing to those Update options vanishes after hours. Is this only supposed to work while the session is open? (I only use it that way, yes, but I'm surprised I can't validate the tool after hours.)
Thanks and NO RUSH
Pieter
Thu Mar 29, 2018 1:40 pm (PDT) . Posted by:
OK .. weekend can't start soon enough.
Both issues from prior note resolved. Minor edit to Layout, meant my cell references entered in Array were one off across the board.
SMF menu item appears, if I give it enough room to show.
PS ... for large arrays, its nice to use Update Worksheet in any case where that's suitable.
ALSO THOUGH
.. ran into this for frequent updating (I don't want every second) but is this a viable appendage to automate the refresh process? The code is very brief. Be sure to use regular straight "" symbols not curlies if you try it via copy and paste of the code.
http://www.exceltip.com/tips/auto-refresh-excel-every-1-second-using-vba-in-excel.html http://www.exceltip.com/tips/auto-refresh-excel-every-1-second-using-vba-in-excel.html
Thanks!
Thu Mar 29, 2018 2:04 pm (PDT) . Posted by:
For timed updates, take a look at the VBA code in the
RCHGetYahooQuotes-Example-Timed-Update.xls template.
On Thu, Mar 29, 2018 at 1:40 PM, pieter_biz@
...
wrote:
>
> OK .. weekend can't start soon enough.
> Both issues from prior note resolved. Minor edit to Layout, meant my
> cell references entered in Array were one off across the board.
>
> SMF menu item appears, if I give it enough room to show.
>
> PS ... for large arrays, its nice to use Update Worksheet in any case
> where that's suitable.
>
> ALSO THOUGH
>
> . ran into this for frequent updating (I don't want every second) but is
> this a viable appendage to automate the refresh process? The code is
> very brief. Be sure to use regular straight "" symbols not curlies if you
> try it via copy and paste of the code.
>
> http://www.exceltip.com/tips/auto-refresh-excel-every-1-
> second-using-vba-in-excel.html
>
>
>
>
Thu Mar 29, 2018 1:37 pm (PDT) . Posted by:
Randy
=smfGetYahooJSONField(C1,"balanceSheetHistory","quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.0.inventory.raw") where c1 is SYMBOL works great in my excel sheet.
However, When I look in the current version of the XLA file, and search for a function named "smfGetYahooJSONField" it returns nothing. I'm hoping someone can point me to the VBA source code for the noted function so that I can drop that into our Access database.
What am I missing?
Carl
Thu Mar 29, 2018 1:58 pm (PDT) . Posted by:
It's in the modGetYahooJSONData module, but I'm sure you'll need a few
other modules. Definitely modJSONExtract and smfUtilities.
However, that function is not a good way to extract a number of fields.
You'd be better off parsing the entire JSON file directly, as a text file,
and extracting items as needed. That's what I did with the
smfGetYahooPortfolioView() function.
On Thu, Mar 29, 2018 at 1:37 PM, carlmims@
...
wrote:
>
> =smfGetYahooJSONField(C1,"balanceSheetHistory","quoteSummary.result.0.
> balanceSheetHistory.balanceSheetStatements.0.inventory.raw") where c1 is
> SYMBOL works great in my excel sheet.
>
> However, When I look in the current version of the XLA file, and search
> for a function named "smfGetYahooJSONField" it returns nothing. I'm
> hoping someone can point me to the VBA source code for the noted function
> so that I can drop that into our Access database.
>
> What am I missing?
>
>
