Minggu, 05 November 2017

[smf_addin] Digest Number 4212[1 Attachment]

14 Messages

Digest #4212
1a
Re: Running test file by "Yahoo!" lewglenn
1b
Re: Running test file [1 Attachment] by "Randy Harmelink" rharmelink
2.1
Re: RCHGetYahooQuotes(B6:B50,C4:V4) by "Randy Harmelink" rharmelink
3a
Re: Google Finance by "Bernie Dudek" berniedudek
3b
Re: Google Finance by "Randy Harmelink" rharmelink
3c
Re: Google Finance by "Bernie Dudek" berniedudek
3d
Re: Google Finance by "Randy Harmelink" rharmelink
3e
Re: Google Finance by "Bernie Dudek" berniedudek
4b
Re: Most Efficient Pull of Barchart Data by "Randy Harmelink" rharmelink
4c
Re: Most Efficient Pull of Barchart Data by "Brad Reel" reeldeal9090
4d
Re: Most Efficient Pull of Barchart Data by "Randy Harmelink" rharmelink

Messages

Sun Nov 5, 2017 2:57 pm (PST) . Posted by:

"Yahoo!" lewglenn

I did exactly what you said and it now seems to work; I've attached the results of step 10.
But now the add-in points to the Documents folder and not the SMF Add-in. Should I move the pointer to again point to SMF Add-in? Can you explain exactly what happened?
Also, it's still the case that PREVIOUS DATE'S PRICE & 50_DAY SMA are not yet available. Correct?
Again, many thanks for your help.

On Saturday, November 4, 2017, 6:38:55 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

 

OK. Try this:
1. Go to the add-in manager and un-check the add-in. All of them if there are more than one.2. Exit EXCEL3. Unzip the 2017.11.02 ZIP file into the "​C:\Users\lewglenn\Documents" folder, replacing files as necessary.4. Restart EXCEL5. Go to the add-in manager and "Browse" to the XLA file in the "​C:\Users\lewglenn\Documents" folder, selecting it6. Use the "Version" formula and check the version7. Exit EXCEL8. Restart EXCEL9. Check the version again10. Try the test XLS file and run the macro. What results do you see?

On Sat, Nov 4, 2017 at 6:18 PM, lewglenn@​... wrote:

I just once again downloaded the latest version of the zip file  from http://ogres-crypt.com/SMF/ I then restarted my computer and ran your test file:

Sub Test()

SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _
       & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _
       & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _
       & "DGRW,REML&quot;

Range("A2:I70") = smfGetYahooPortfolioView( SelStr, "013518192015163535", , 1)
Range("A1") = RCHGetElementNumber("Version&quot;)

End Sub.

The result from RCHGetElementNumber("Version&quot;) is:

Stock Market Functions add-in, Version 2.1.2017.09.17 (
​​C:\Users&#92;lewglenn&#92;Documents; Windows (32-bit) NT 6.02; 12.0; ; ; 1)

I'm at my wits end. Don't know what else I can do to get the latest version of RCH_Stock_Market_Functions. xla. When I examine the properties of the file that I just downloaded it says Created: Thursday, ‎November ‎02, ‎2017, ‏‎10:07:18 AM. The same for Modified and Accessed.

Any thoughts about what I might be doing wrong?

#yiv3626662192 #yiv3626662192 -- #yiv3626662192ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3626662192 #yiv3626662192ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3626662192 #yiv3626662192ygrp-mkp #yiv3626662192hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3626662192 #yiv3626662192ygrp-mkp #yiv3626662192ads {margin-bottom:10px;}#yiv3626662192 #yiv3626662192ygrp-mkp .yiv3626662192ad {padding:0 0;}#yiv3626662192 #yiv3626662192ygrp-mkp .yiv3626662192ad p {margin:0;}#yiv3626662192 #yiv3626662192ygrp-mkp .yiv3626662192ad a {color:#0000ff;text-decoration:none;}#yiv3626662192 #yiv3626662192ygrp-sponsor #yiv3626662192ygrp-lc {font-family:Arial;}#yiv3626662192 #yiv3626662192ygrp-sponsor #yiv3626662192ygrp-lc #yiv3626662192hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3626662192 #yiv3626662192ygrp-sponsor #yiv3626662192ygrp-lc .yiv3626662192ad {margin-bottom:10px;padding:0 0;}#yiv3626662192 #yiv3626662192actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3626662192 #yiv3626662192activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3626662192 #yiv3626662192activity span {font-weight:700;}#yiv3626662192 #yiv3626662192activity span:first-child {text-transform:uppercase;}#yiv3626662192 #yiv3626662192activity span a {color:#5085b6;text-decoration:none;}#yiv3626662192 #yiv3626662192activity span span {color:#ff7900;}#yiv3626662192 #yiv3626662192activity span .yiv3626662192underline {text-decoration:underline;}#yiv3626662192 .yiv3626662192attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3626662192 .yiv3626662192attach div a {text-decoration:none;}#yiv3626662192 .yiv3626662192attach img {border:none;padding-right:5px;}#yiv3626662192 .yiv3626662192attach label {display:block;margin-bottom:5px;}#yiv3626662192 .yiv3626662192attach label a {text-decoration:none;}#yiv3626662192 blockquote {margin:0 0 0 4px;}#yiv3626662192 .yiv3626662192bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3626662192 .yiv3626662192bold a {text-decoration:none;}#yiv3626662192 dd.yiv3626662192last p a {font-family:Verdana;font-weight:700;}#yiv3626662192 dd.yiv3626662192last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3626662192 dd.yiv3626662192last p span.yiv3626662192yshortcuts {margin-right:0;}#yiv3626662192 div.yiv3626662192attach-table div div a {text-decoration:none;}#yiv3626662192 div.yiv3626662192attach-table {width:400px;}#yiv3626662192 div.yiv3626662192file-title a, #yiv3626662192 div.yiv3626662192file-title a:active, #yiv3626662192 div.yiv3626662192file-title a:hover, #yiv3626662192 div.yiv3626662192file-title a:visited {text-decoration:none;}#yiv3626662192 div.yiv3626662192photo-title a, #yiv3626662192 div.yiv3626662192photo-title a:active, #yiv3626662192 div.yiv3626662192photo-title a:hover, #yiv3626662192 div.yiv3626662192photo-title a:visited {text-decoration:none;}#yiv3626662192 div#yiv3626662192ygrp-mlmsg #yiv3626662192ygrp-msg p a span.yiv3626662192yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3626662192 .yiv3626662192green {color:#628c2a;}#yiv3626662192 .yiv3626662192MsoNormal {margin:0 0 0 0;}#yiv3626662192 o {font-size:0;}#yiv3626662192 #yiv3626662192photos div {float:left;width:72px;}#yiv3626662192 #yiv3626662192photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3626662192 #yiv3626662192photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3626662192 #yiv3626662192reco-category {font-size:77%;}#yiv3626662192 #yiv3626662192reco-desc {font-size:77%;}#yiv3626662192 .yiv3626662192replbq {margin:4px;}#yiv3626662192 #yiv3626662192ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3626662192 #yiv3626662192ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3626662192 #yiv3626662192ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3626662192 #yiv3626662192ygrp-mlmsg select, #yiv3626662192 input, #yiv3626662192 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3626662192 #yiv3626662192ygrp-mlmsg pre, #yiv3626662192 code {font:115% monospace;}#yiv3626662192 #yiv3626662192ygrp-mlmsg * {line-height:1.22em;}#yiv3626662192 #yiv3626662192ygrp-mlmsg #yiv3626662192logo {padding-bottom:10px;}#yiv3626662192 #yiv3626662192ygrp-msg p a {font-family:Verdana;}#yiv3626662192 #yiv3626662192ygrp-msg p#yiv3626662192attach-count span {color:#1E66AE;font-weight:700;}#yiv3626662192 #yiv3626662192ygrp-reco #yiv3626662192reco-head {color:#ff7900;font-weight:700;}#yiv3626662192 #yiv3626662192ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3626662192 #yiv3626662192ygrp-sponsor #yiv3626662192ov li a {font-size:130%;text-decoration:none;}#yiv3626662192 #yiv3626662192ygrp-sponsor #yiv3626662192ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3626662192 #yiv3626662192ygrp-sponsor #yiv3626662192ov ul {margin:0;padding:0 0 0 8px;}#yiv3626662192 #yiv3626662192ygrp-text {font-family:Georgia;}#yiv3626662192 #yiv3626662192ygrp-text p {margin:0 0 1em 0;}#yiv3626662192 #yiv3626662192ygrp-text tt {font-size:120%;}#yiv3626662192 #yiv3626662192ygrp-vital ul li:last-child {border-right:none !important;}#yiv3626662192
Attachment(s) from Yahoo!
1 of 1 File(s)

Sun Nov 5, 2017 3:16 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I haven't a clue what you mean by "​the add-in points to the Documents
folder and not the SMF Add-in".

Do you mean the "C:\Users\lewglenn\Documents" folder? If so, that's where
you had it before, so that's where I told you to put it again. I think
you'd be better off with it in the "C:\SMF Add-in" folder. Otherwise, you
end up having to do smfFixLinks with any template you get from me.
According to the "Version" output in your attached workbook, you installed
the add-in in the "C:\Users\lewglenn\Desktop\Test" folder. But any time you
have the add-in located in a location other then when the workbook was
saved, you'll need to manually fix the unresolved links (i.e. run
smfFixLinks), because EXCEL saves the location of the add-in with every
add-in function whenever a workbook is saved.

"Prev Close" and "50-DMA" are available, but only if you have the latest
version of the add-in. See:

https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171104.html

On Sun, Nov 5, 2017 at 3:57 PM, Yahoo! lewglenn@
​...
wrote:

>
> I did exactly what you said and it now seems to work; I've attached the
> results of step 10.
>
> But now
> ​​
> the add-in points to the Documents folder and not the SMF Add-in. Should I
> move the pointer to again point to SMF Add-in? Can you explain exactly what
> happened?
>
> Also, it's still the case that PREVIOUS DATE'S PRICE & 50_DAY SMA are not
> yet available. Correct?
>
> Again, many thanks for your help.
>

Sun Nov 5, 2017 3:05 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

See:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171104.html

The new version of the add-in and the new template have "% Change" as field
#52.

On Sun, Nov 5, 2017 at 3:51 PM, weldencd@
​...
wrote:

>
> I'm still working on converting to this new template. I previously used
> the "% Change" in my calculations. I don't see this in the new template. I
> see the "Change" column which I assume is in $. However, I assume the "%
> Change" was calculated based on the closing price from the previous day. I
> don't see anything but Open, High, and Low. Is there a way to calculate the
> value I want?
>

Sun Nov 5, 2017 3:09 pm (PST) . Posted by:

"Bernie Dudek" berniedudek

I turn off the macro by unchecking the box next to it I'm clicking on the SMF Force Recacluation Icon shown next to the printer icon. I don't understand why it's looking for the macro in C:\SMF not in C:\SMF Add-in.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Sunday, November 05, 2017 4:48 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Google Finance

What are you clicking on? A button on your quick access toolbar?

I'm also confused by your comment that you "turned off the macro". I'm not sure what that means.

On Sun, Nov 5, 2017 at 3:37 PM, 'Bernie Dudek' bdudek1172@comcast.net [smf_addin] <smf_addin@yahoogroups.com> wrote:

It seems to be working but I have an issue with updating the macro. When I click the SMF Force Recalculation symbol I get

I have turned off the macro, closed the spreadsheet and re-added the macro which is in C:\SMF Add-in. It still gives me the error. The message says it's looking for the macro in C:\SMF not in C:\SMF Add-in. How can I correct this error?

Sun Nov 5, 2017 3:32 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Ah, OK. So you're checking and unchecking the add-in within the add-in
manager, not the macro.

That button on your toolbar will be looking for the macro in a specific
location -- where the add-in was located when you created the button. If
you move the add-in to another location, you'd need to delete that button
and re-add it based on the new location.

On Sun, Nov 5, 2017 at 4:09 PM, 'Bernie Dudek' bdudek1172@
​...
wrote:

>
>
> ​​
> I turn off the macro by unchecking the box next to it I'm clicking on
> the SMF Force Recacluation Icon shown next to the printer icon. I don't
> understand why it's looking for the macro in C:\SMF not in C:\SMF Add-in.
>
>

Sun Nov 5, 2017 4:12 pm (PST) . Posted by:

"Bernie Dudek" berniedudek

I removed it from the toolbar. Dumb question, how do I re-add it?

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Sunday, November 05, 2017 5:33 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Google Finance

Ah, OK. So you're checking and unchecking the add-in within the add-in manager, not the macro.

That button on your toolbar will be looking for the macro in a specific location -- where the add-in was located when you created the button. If you move the add-in to another location, you'd need to delete that button and re-add it based on the new location.

On Sun, Nov 5, 2017 at 4:09 PM, 'Bernie Dudek' bdudek1172@

​...

wrote:

​​

I turn off the macro by unchecking the box next to it I'm clicking on the SMF Force Recacluation Icon shown next to the printer icon. I don't understand why it's looking for the macro in C:\SMF not in C:\SMF Add-in.

Sun Nov 5, 2017 4:18 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Google is your friend. :)

https://support.office.com/en-us/article/Assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c

On Sun, Nov 5, 2017 at 5:12 PM, 'Bernie Dudek' bdudek1172@
​...
wrote:

>
> I removed it from the toolbar. Dumb question, how do I re-add it?
>

Sun Nov 5, 2017 4:26 pm (PST) . Posted by:

"Bernie Dudek" berniedudek

Life is good. Thanks!!

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Sunday, November 05, 2017 6:18 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Google Finance

Google is your friend. :)

https://support.office.com/en-us/article/Assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c

On Sun, Nov 5, 2017 at 5:12 PM, 'Bernie Dudek' bdudek1172@

​...

wrote:

I removed it from the toolbar. Dumb question, how do I re-add it?

Sun Nov 5, 2017 3:11 pm (PST) . Posted by:

brad.reel

Randy, been thinking about what you said as far as being efficient and minimizing data pulls, etc. If I have these 5 stocks:


INDL
XPP
EET
EWI
ROM


and want these three element #'s: 1266, 1269, 1223 from BarChart


What is the most efficient way for me to pull that data? Thanks!

Sun Nov 5, 2017 3:55 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I'm not sure there's anything you can do to be more efficient there.
Pulling those three elements for those five companies would mean you need
to pull five web pages (one per ticker symbol). In most cases, the add-in
saves a web page before it extracts data from it, so that if you do get
multiple items from the same web page, it only needs to be retrieved once.
That's why the smfForceRecalculation macro is needed -- it purges all saved
copies of web pages, forcing the add-in to have to retrieve a fresh copy of
a web page for each one it needs.

If you had a large number of ticker symbols, I might suggest creating a
portfolio or watch list for them in barchart. Then you could manually
export a view that would contains those fields. Barchart limits free
membership to 10 downloads per day and I think each view is limited to 12
fields? So far, I haven't seen a way to automate that exporting with the
add-in.

*Oh no! Shame on you for asking. It appears the Barchart watchlist web page
display is fed by a JSON file similar to Yahoo's portfolio view. I never
thought to look before. I may be able to simply feed the API a list of
ticker symbols and field names...*

On Sun, Nov 5, 2017 at 4:11 PM, brad.reel@
​...
wrote:

> Randy, been thinking about what you said as far as being efficient and
> minimizing data pulls, etc. If I have these 5 stocks:
>
> INDL
>
> XPP
>
> EET
>
> EWI
>
> ROM
>
> and want these three element #'s: 1266, 1269, 1223 from BarChart
>
> What is the most efficient way for me to pull that data? Thanks!
>

Sun Nov 5, 2017 4:03 pm (PST) . Posted by:

"Brad Reel" reeldeal9090

I didn't mean to create work for you... :)

Let me know how it goes.

On Sun, Nov 5, 2017 at 5:55 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> I'm not sure there's anything you can do to be more efficient there.
> Pulling those three elements for those five companies would mean you need
> to pull five web pages (one per ticker symbol). In most cases, the add-in
> saves a web page before it extracts data from it, so that if you do get
> multiple items from the same web page, it only needs to be retrieved once.
> That's why the smfForceRecalculation macro is needed -- it purges all saved
> copies of web pages, forcing the add-in to have to retrieve a fresh copy of
> a web page for each one it needs.
>
> If you had a large number of ticker symbols, I might suggest creating a
> portfolio or watch list for them in barchart. Then you could manually
> export a view that would contains those fields. Barchart limits free
> membership to 10 downloads per day and I think each view is limited to 12
> fields? So far, I haven't seen a way to automate that exporting with the
> add-in.
>
> *Oh no! Shame on you for asking. It appears the Barchart watchlist web
> page display is fed by a JSON file similar to Yahoo's portfolio view. I
> never thought to look before. I may be able to simply feed the API a list
> of ticker symbols and field names...*
>
> On Sun, Nov 5, 2017 at 4:11 PM, brad.reel@
> ​...
> wrote:
>
>> Randy, been thinking about what you said as far as being efficient and
>> minimizing data pulls, etc. If I have these 5 stocks:
>>
>> INDL
>>
>> XPP
>>
>> EET
>>
>> EWI
>>
>> ROM
>>
>> and want these three element #'s: 1266, 1269, 1223 from BarChart
>>
>> What is the most efficient way for me to pull that data? Thanks!
>>
>
>
>

Sun Nov 5, 2017 4:15 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Soon to come ..

smfGetBarchartPortfolioData()

I may be able to pattern it after smfGetYahooPortfolioView() as the flow
would be almost identical -- build ticker list, build field list, process
JSON file by line and by field. One difference is that Barchart has
hundreds of fields, so a 2-digit field number won't work.

And it would provide another alternative source for current quotes!

Odd thing is that I did find it in some old notes from months ago. But the
context of smfGetYahooPortfolioView() wasn't fresh in mind. Now that I've
gone through the process once, by necessity...

On Sun, Nov 5, 2017 at 5:03 PM, Brad Reel brad.reel90@
​...
wrote:

>
> I didn't mean to create work for you... :)
>
> Let me know how it goes.
>
> On Sun, Nov 5, 2017 at 5:55 PM, Randy Harmelink rharmelink@
> ​
> <rharmelink@gmail.com>.
> ​..​
> wrote:
>
>>
>>
>> *Oh no! Shame on you for asking. It appears the Barchart watchlist web
>> page display is fed by a JSON file similar to Yahoo's portfolio view. I
>> never thought to look before. I may be able to simply feed the API a list
>> of ticker symbols and field names...*
>>
>> On Sun, Nov 5, 2017 at 4:11 PM, brad.reel@
>> ​...
>> wrote:
>>
>>> Randy, been thinking about what you said as far as being efficient and
>>> minimizing data pulls, etc. If I have these 5 stocks:
>>>
>>> INDL
>>>
>>> XPP
>>>
>>> EET
>>>
>>> EWI
>>>
>>> ROM
>>>
>>> and want these three element #'s: 1266, 1269, 1223 from BarChart
>>>
>>> What is the most efficient way for me to pull that data? Thanks!
>>>
>>

Sun Nov 5, 2017 4:46 pm (PST) . Posted by:

u1704141

one word I have for you.. Genius

It worked.. Many Thanks

Sun Nov 5, 2017 6:40 pm (PST) . Posted by:

jimmymc00


Randy. I just downloaded the 11-4 add-in version and it picked up right where the 11-2 version left off. Fantastic!

I'm blown away by your expertise, but mostly by your dedication to making this add-in a workable tool for everyone. I'm sure that I and many others are very grateful for what you are doing and are sleeping better at night as a result. Speaking of sleeping, based on your comment stream it doesn't look like you are getting much yourself. I hope that changes for you soon.

Thank you very much for such a super product and all your help. Jim

-----Original Message-----
From: Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com>
To: smf_addin <smf_addin@yahoogroups.com>
Sent: Sun, Nov 5, 2017 5:05 pm
Subject: Re: [smf_addin] Re: smfGetPortfolioView() Trying to use Field "51"

You'll need the 2017.11.04 version of the add-in to use the "51" field number.

On Sun, Nov 5, 2017 at 2:41 PM, u1704141@
​...
wrote:

I transitioned over to using smfGetPortfolioView for Yahoo Quotes. =smfGetYahooPortfolioView(C9,"15") works fine pulling it current quote. However, I'm not having success using field "51" which I think is the previous close quote... =smfGetYahooPortfolioView(C9,"51"). Any thoughts on perhaps it's not pulling? The cell contains '-' when trying to execute that field..

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 18.43

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)