Kamis, 09 November 2017

[smf_addin] Digest Number 4222[1 Attachment]

15 Messages

Digest #4222
1b
Re: smfGetBarchartPortfolioView example by "Randy Harmelink" rharmelink
1c
Re: smfGetBarchartPortfolioView example by "Randy Harmelink" rharmelink
1e
Re: smfGetBarchartPortfolioView example by "Randy Harmelink" rharmelink

Messages

Wed Nov 8, 2017 8:46 pm (PST) . Posted by:

ehrlichk

I downloaded the smfGetBarchartPortfolioView example spreadsheet after installing the 11/8 version.


RCHGetElementNumber("Version") returns "Stock Market Functions add-in, Version 2.1.2017.11.08 (C:\SMF Add-In; Windows (32-bit) NT 10.00; 16.0; ; ; 1)"


=smfGetBarchartPortfolioView only returns the headers.
All other cells in the array show -- .


SMFForceRecalculation does not help.


What am I doing wrong?


Thanks!

Wed Nov 8, 2017 11:32 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Oops. Forgot that you need to do the free registration on their web site
and create an IE security cookie for EXCEL and the add-in to use.

On Wed, Nov 8, 2017 at 9:46 PM, ehrlichk@
​...
wrote:

> I downloaded the smfGetBarchartPortfolioView example spreadsheet after
> installing the 11/8 version.
>
> RCHGetElementNumber("Version") returns "Stock Market Functions add-in,
> Version 2.1.2017.11.08 (C:\SMF Add-In; Windows (32-bit) NT 10.00; 16.0; ; ;
> 1)"
>
> =smfGetBarchartPortfolioView only returns the headers.
>
> All other cells in the array show -- .
>
> SMFForceRecalculation does not help.
>
> What am I doing wrong?
>
>
>

Wed Nov 8, 2017 11:44 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Hmmm. Scratch that. I just tried the JSON file with a browser without
logging in and the file was delivered fine. You DO need to be logged in to
use the Watchlist and Portfolio features, but apparently don't need to be
logged in to use the JSON files that feeds them.

What do you get with:

=smfGetBarchartPortfolioView("MMM","009")
=RCHGetWebData("
https://core-api.barchart.com/v1/quotes/get?symbols=MMM&fields=symbol,lastPrice
")

On Wed, Nov 8, 2017 at 9:46 PM, ehrlichk@
​...
wrote:

> I downloaded the smfGetBarchartPortfolioView example spreadsheet after
> installing the 11/8 version.
>
> RCHGetElementNumber("Version") returns "Stock Market Functions add-in,
> Version 2.1.2017.11.08 (C:\SMF Add-In; Windows (32-bit) NT 10.00; 16.0; ; ;
> 1)"
>
> =smfGetBarchartPortfolioView only returns the headers.
>
> All other cells in the array show -- .
>
> SMFForceRecalculation does not help.
>
> What am I doing wrong?
>
>
>

Thu Nov 9, 2017 7:33 am (PST) . Posted by:

pauljl52

I am having the exact same problem. All my sheets with smfGetYahooPortfolioView are working fine, smfForceRecalculation macro also working fine here



I have removed ticker symbol none which did not help.


I tried the examples above and those on the Documentation page
smfGetBarchartPortfolioView-Function.html only returns the headers. All other cells in the array show -- .


I also have the same result with this sheet smfGetGuruFocusCSVItem-Examples.xls
only returns the headers. All other cells in the array show -- .
I did remove None and replaced it with a symbol


Thanks so much for your quick response to the "RCHGetYahooQuotes() problems".


Thu Nov 9, 2017 7:51 am (PST) . Posted by:

"Randy Harmelink" rharmelink

*Re: smfGetBarchartPortfolioView()*

Same question -- what do you get with:

=RCHGetElementNumber("Version")
=smfGetBarchartPortfolioView("MMM","009")
=RCHGetWebData("
https://core-api.barchart.com/v1/quotes/get?symbols=MMM&fields=symbol,lastPrice
")

*​Re: ​​smfGetGuruFocusCSVItem-Examples.xls *

​Did you create the necessary security cookie for EXCEL and the add-in? You
need to use EXCEL's IE object (alt+d+d+w)​ and log into your GuruFocus
account before the function can do anything. You need to be a subscriber to
grab the necessary CSV file.

On Thu, Nov 9, 2017 at 6:57 AM, pauljl52@
​...
wrote:

> I am having the exact same problem. All my sheets with
> smfGetYahooPortfolioView are working fine, smfForceRecalculation macro
> also working fine here
>
> I have removed ticker symbol none which did not help.
>
> I tried the examples above and those on the Documentation page
>
> *smfGetBarchartPortfolioView-Function.html *only returns the headers. All
> other cells in the array show -- .
>
> I also have the same result with this sheet
> ​​
> smfGetGuruFocusCSVItem-Examples.xls
> only returns the headers. All other cells in the array show -- .
>
> I did remove None and replaced it with a symbol
>
> Thanks so much for your quick response to the "RCHGetYahooQuotes()
> problems".
>
>
>

Thu Nov 9, 2017 10:04 am (PST) . Posted by:

pauljl52

I get the following results:

For: =RCHGetElementNumber("Version")

I get:

Stock Market Functions add-in, Version 2.1.2017.11.08 (C:\SMF Add-in; Windows (32-bit) NT 5.01; 10.0; ; ; 1)
For: =smfGetBarchartPortfolioView("MMM","009")

I get:
--
For
=RCHGetWebData("https://core-api.barchart.com/v1/quotes/get?symbols=MMM&fields=symbol,lastPrice https://core-api.barchart.com/v1/quotes/get?symbols=MMM&fields=symbol,lastPrice")

I Get: empty cell

Re: ​ smfGetGuruFocusCSVItem-Examples.xls

You need to be a subscriber to grab the necessary CSV file.

I am not a subscriber.

When you answered :
Hmmm. Scratch that. I just tried the JSON file with a browser without logging in and the file was delivered fine

I thought that might apply to both GetGuruFocus and GetBarChart

Thanks





Wed Nov 8, 2017 11:35 pm (PST) . Posted by:

alberto.ratti

You are right. The output of Field Numbers #72#,76 is ok. Thanks a lot!

Thu Nov 9, 2017 3:18 am (PST) . Posted by:

a.bereziuk

Randy, which website would you recommend to use to extract quotes (Last price) with smfGetTagContent func? I normally have only 5-7 positions in the portfolio, so I extract them separately.

Thu Nov 9, 2017 7:58 am (PST) . Posted by:

"Randy Harmelink" rharmelink

smfGetYahooPortfolioView() is designed to grab multiple quotes and multiple
data items in a single Internet request.

5-7 quotes isn't really an issue. I'm more worried about people doing
dozens or hundreds.

You can do multiple quotes from FinzViz in one shot:

=smfGetTagContent("https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM
","td",1,"?t=MMM&",">Price",,,1)
=smfGetTagContent("https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM
","td",1,"?t=IBM&",">Price",,,1)

You just need to use the same URL on both requests. Just create a URL with
a list of desired ticker symbols, then search by ticker before search for
the price.

Hmm. I could make an smfGetFinvizPortfolioView(). A lot of fields there. :)

On Thu, Nov 9, 2017 at 4:18 AM, a.bereziuk@
​...
wrote:

>
> Randy, which website would you recommend to use to extract quotes (Last
> price) with smfGetTagContent func? I normally have only 5-7 positions in
> the portfolio, so I extract them separately.
>

Thu Nov 9, 2017 9:41 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

Randy, if you are concerned about folks trying to do hundreds of items at one time.
Why can't you code in a max value and only retrieve that many items.?


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Thursday, November 09, 2017 10:59 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] show me the correct RCHGetTableCell function to use


smfGetYahooPortfolioView() is designed to grab multiple quotes and multiple data items in a single Internet request.

5-7 quotes isn't really an issue. I'm more worried about people doing dozens or hundreds.

You can do multiple quotes from FinzViz in one shot:

=smfGetTagContent("https://finviz.com/quote.ashx?ta=1 <https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM> &p=d&t=MMM,IBM","td",1,"?t=MMM&",">Price",,,1)
=smfGetTagContent("https://finviz.com/quote.ashx?ta=1 <https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM> &p=d&t=MMM,IBM";,"td",1,"?t=IBM&",">Price",,,1)

You just need to use the same URL on both requests. Just create a URL with a list of desired ticker symbols, then search by ticker before search for the price.

Hmm. I could make an smfGetFinvizPortfolioView(). A lot of fields there. :)

On Thu, Nov 9, 2017 at 4:18 AM, a.bereziuk@
​...
wrote:

Randy, which website would you recommend to use to extract quotes (Last price) with smfGetTagContent func? I normally have only 5-7 positions in the portfolio, so I extract them separately.

Thu Nov 9, 2017 5:09 am (PST) . Posted by:

alberto.ratti

Randy,
please have a look at the latest fix.
I think you have to remove the items "58", "61", "65", and "68" from the "Case" instruction.
The current output (see attached shot) is the same of the previous version.
Thanks!
Alberto


Attachment(s) from
1 of 1 Photo(s)

Thu Nov 9, 2017 7:42 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Thanks for the heads up -- changes are ready to be released. See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/33805

On Thu, Nov 9, 2017 at 6:09 AM, alberto.ratti@
​...
wrote:

> please have a look at the latest fix.
>
> I think you have to remove the items "58", "61", "65", and "68" from the
> "Case" instruction.
>
> The current output (see attached shot) is the same of the previous version.
>
>
>

Thu Nov 9, 2017 6:34 am (PST) . Posted by:

john_hoel

Pete,

Would it be possible to create a name for the resulting range (A3:...) in the DataTable sheet so that a reference to the range from another workbook can use the name (instead of needing to know the actual range)? I can add some code to yours to make this happen, but if you were to ever update this tool, I would lose my addition.

Many thanks in advance.

Thu Nov 9, 2017 10:01 am (PST) . Posted by:

john_hoel

Pete,

I added the following code snippet immediately before the End Sub statement of LoadsmfYahooPortfolioData(), tested it, and it works.

' JDH Create named range JDHDataTable
Dim idx As Long ' JDH
Dim nm As Name ' JDH
Dim cell As Range ' JDH
Dim rng As Range ' JDH
Dim RangeName As String ' JDH

' First delete existing name if it exists
RangeName = "JDHDataTable&quot;
For Each nm In WBResults.Names
If nm.Name = RangeName Then
nm.Delete
Exit For
End If
Next
' Create named range
idx = i
Do While idx > 3
If StrComp(Left(Range("A" & idx).Value, 1), "-", vbTextCompare) <> 0 Then
If StrComp(Left(Range("A" & idx).Value, 1), "", vbTextCompare) <> 0 Then
Exit Do
End If
End If
idx = idx - 1
Loop
Set cell = WSResults.Range("A3:" & sColumnLetters & idx)
WBResults.Names.Add Name:=RangeName, RefersTo:=cell
' end JDH
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 10.04

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)