Minggu, 08 Mei 2016

[smf_addin] Digest Number 3693

10 Messages

Digest #3693

Messages

Sat May 7, 2016 8:19 am (PDT) . Posted by:

ethanbond911

J Teske,
Thank you for the help. I found the links very useful. Basically I want to build my own data base for SPX. I want to check out the Prices of Conversions, Reversals, jelly rolls and boxes as provided by my broker.
Once again J, much appreciated. Have a great day.
Ethan

Sat May 7, 2016 9:42 am (PDT) . Posted by:

kjtemplin

I recently updated my Excel to Office 365 - it was free to try. Previously I was using
Excel 2010. I was able to get the SMF_Addin to work on the common data elements but the Morningstar Premium data elements I need are not being downloaded. I have tried just about everything I know (update web query, IE and Edge logon to Morningstar) but still no download. The 2010 Excel version still works fine but for some reason I can get the latest Excel version to download these premium data elements.


Any help would be most appreciated.


Thanks,
Ken

Sat May 7, 2016 11:08 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

When you use the EXCEL Web Query process, can you see the premium data
items?

What formulas are you using?

On Sat, May 7, 2016 at 9:42 AM, kjtemplin@
​...wrote:

> I recently updated my Excel to Office 365 - it was free to try.
> Previously I was using
>
> Excel 2010. I was able to get the SMF_Addin to work on the common data
> elements but the Morningstar Premium data elements I need are not being
> downloaded. I have tried just about everything I know (update web query,
> IE and Edge logon to Morningstar) but still no download. The 2010 Excel
> version still works fine but for some reason I can get the latest Excel
> version to download these premium data elements.
>
> Any help would be most appreciated.
>
>

Sat May 7, 2016 2:50 pm (PDT) . Posted by:

kjtemplin

No, not in Excel for Office 365. The formula returns the result "Undefined".

The formula is =RCHGetElementNumber(Symbol, Element Number). This works just fine in Excel 2010 but it does not work in Excel for Office 365.


Thanks
Ken


Sat May 7, 2016 4:14 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sounds like you installed the add-in incorrectly on your new machine.
"Undefined" mean you don't have the element definitions files (i.e.
smf-elements-*.txt) in the same folder as the XLA file.

On Sat, May 7, 2016 at 2:50 PM, kjtemplin@
​...wrote:

>
> No, not in Excel for Office 365. The formula returns the result
> "Undefined".
>
> The formula is =RCHGetElementNumber(Symbol, Element Number). This works
> just fine in Excel 2010 but it does not work in Excel for Office 365.
>
>

Sat May 7, 2016 8:08 pm (PDT) . Posted by:

kjtemplin

Randy:

Thanks. That was my error. It is now fixed and works fine.


Again, thanks for the help.
Ken


Sat May 7, 2016 12:28 pm (PDT) . Posted by:

chris.heidbreder@ymail.com

Randy,


Thanks for the quick response to my previous questions. I have a follow up: when referencing finviz industries only 20 tickers are shown per page. How do you alter the URL or function to pull information from the remaining pages?


For instance:
No. 1 =RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_"&$B$3,2,">"&1&"<")
-Thru-
No. 20 =RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_"&$B$3,2,">"&20&"<")
No. 21 ? (Page 2)


Again, thanks in advance,
CH


Chris Heidbreder <chris.heidbreder@ymail.com> mailto:chris.heidbreder@ymail.com
To rharmelink@gmail.com mailto:rharmelink@gmail.com




May 6 at 10:35 PM


Randy,


I am pulling my hair out trying to figure out how to extract particular rows and columns of data from Finviz.com screener pages. I started using a forum members 'Finviz Metrics' template and have been successful at pulling Industry Screen info such as Gold Company Names, Country, Sector, Industry, Market Cap, and Volume, but I cant for the life of me retrieve ticker, price, and price change. Could it be because this particular information the Finviz provides is being pulled from another source or is in a different format? If you know whats going on or could point me to another reference source, that would be a big help. Thanks is advance. Attached is my draft copy of the excel file.









Randy Harmelink <rharmelink@gmail.com> mailto:rharmelink@gmail.com
To Chris Heidbreder mailto:chris.heidbreder@ymail.com


Here are the formulas I would use:


C7: =smfGetTagContent("http://finviz.com/screener.ashx?v=111&f=ind_ http://finviz.com/screener.ashx?v=111&f=ind_"&$B$2,"a",1,">"&$B7&"<")

H7: =smfConvertData(smfStrExtr(RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_ http://finviz.com/screener.ashx?v=111&f=ind_"&$B$2,8,">"&$B7&"<"),">","~"))
I7: =RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_ http://finviz.com/screener.ashx?v=111&f=ind_"&$B$2,9,">"&$B7&";<")


I'd also change the other columns to be like I7, just for consistency.




PS: You need to send add-in questions to the Yahoo group. I have email filters that direct them to a folder. I found this message in my SPAM folder, which means it could easily have been deleted without me ever seeing it.



Show original message






Sat May 7, 2016 12:50 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The other pages of the screening results use an additional parameter in the
URL, so you need to modify it as you get each set of 20 results. For
example, the page 2 URL is:

http://finviz.com/screener.ashx?v=111&f=ind_gold&r=21

Note the "&r=21" -- that is telling FinViz to display the results starting
at the 21st item. So you need another item in your URL. For example:

C7: =smfGetTagContent("http://finviz.com/screener.ashx?v=111&r=
"&1+20*INT((B7-1)/20)&"&f=ind_"&$B$2,"a",1,">"&$B7&"<")

The "1+20*INT((B7-1)/20)" calculation is determining which page start would
be needed for the item number in cell B7.

On Sat, May 7, 2016 at 12:28 PM, chris.heidbreder@
​...wrote:

> Thanks for the quick response to my previous questions. I have a follow
> up: when referencing finviz industries only 20 tickers are shown per page.
> How do you alter the URL or function to pull information from the remaining
> pages?
>
> For instance:
>
> No. 1 =RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_
> "&$B$3,2,">"&1&"<")
>
> -Thru-
>
> No. 20 =RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_
> "&$B$3,2,">"&20&"<")
>
> No. 21 ? (Page 2)
>
>
> Again, thanks in advance,
>
> CH
>
>
> Chris Heidbreder <chris.heidbreder@ymail.com> <chris.heidbreder@ymail.com>
> To
> rharmelink@gmail.com
> May 6 at 10:35 PM
>
> Randy,
>
> I am pulling my hair out trying to figure out how to extract particular
> rows and columns of data from Finviz.com screener pages. I started using a
> forum members 'Finviz Metrics' template and have been successful at pulling
> Industry Screen info such as Gold Company Names, Country, Sector, Industry,
> Market Cap, and Volume, but I cant for the life of me retrieve ticker,
> price, and price change. Could it be because this particular information
> the Finviz provides is being pulled from another source or is in a
> different format? If you know whats going on or could point me to another
> reference source, that would be a big help. Thanks is advance. Attached is
> my draft copy of the excel file.
>
>
>
>
> Randy Harmelink <rharmelink@gmail.com> <rharmelink@gmail.com>
> To
> Chris Heidbreder <chris.heidbreder@ymail.com>
> Here are the formulas I would use:
>
> C7: =smfGetTagContent("http://finviz.com/screener.ashx?v=111&f=ind_
> "&$B$2,"a",1,">"&$B7&"<")
> H7: =smfConvertData(smfStrExtr(RCHGetTableCell("
> http://finviz.com/screener.ashx?v=111&f=ind_
> "&$B$2,8,">"&$B7&"<"),">","~"))
> I7: =RCHGetTableCell("http://finviz.com/screener.ashx?v=111&f=ind_
> "&$B$2,9,">"&$B7&";<")
>
> I'd also change the other columns to be like I7, just for consistency.
>
> PS: You need to send add-in questions to the Yahoo group. I have email
> filters that direct them to a folder. I found this message in my SPAM
> folder, which means it could easily have been deleted without me ever
> seeing it.
>

Sat May 7, 2016 1:36 pm (PDT) . Posted by:

samil_beret

Hi


I would like to download "Price to Book", "Price to Earnings". Price to Cash Flow" and "Cash Flow Growth" for various ETfs [e.g VTI] from Morningstar. I can get these in a spreadsheet using RCHGetElementNumbers 4681,4684,4690 and 4705.


However, I would like to perform this task using VBA code in a macro [that does other evaluations] and I am having difficulty writing specific VBA statements to download these data.


I would appreciate any help from VBA experts.


Thanks


Samil Beret

Sat May 7, 2016 8:48 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Just use the SMF functions. You just need to add the SMF library as a
reference item for the VBA project. Then, it's as simple as:

Sub Test()
Dim v1 As Variant
v1 = RCHGetElementNumber("VFINX&quot;, 4681)
MsgBox v1
End Sub

On Sat, May 7, 2016 at 1:36 PM, sberet@
​...wrote:

> I would like to download "Price to Book", "Price to Earnings". Price to
> Cash Flow" and "Cash Flow Growth" for various ETfs [e.g VTI] from
> Morningstar. I can get these in a spreadsheet using RCHGetElementNumbers
> 4681,4684,4690 and 4705.
>
> However, I would like to perform this task using VBA code in a macro [that
> does other evaluations] and I am having difficulty writing specific VBA
> statements to download these data.
>
> I would appreciate any help from VBA experts.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Posting Komentar