15 Messages
Digest #4228
7b
Re: Randy, what is smf formulat to get the stock price at finviz web by "Randy Harmelink" rharmelink
Messages
Fri Nov 10, 2017 1:53 pm (PST) . Posted by:
dmohn1
First, I love this app. Thank you Randy. I recently had to upgrade and reinstall courtesy of the Yahoo changes and now my Excel application crashes on startup and shutdown whenever the SMF add-in is loaded. If I start Excel without SMF add-in loaded, and then load the add-in, the SMF functions work beautifully and I can use SmfgetYahooPortfolioView without any problem, until I try to exit Excel and then Excel will crash.
I am using Microsoft Excel 2016 MSO 32-bit and have done the following:
1. I have made c:\SMF add-in a trusted location
2. I have cleared the unblock tab on properties for C:\SMF Add-in\RCH_Stock_Market_Functions.xla
My Excel is running on Windows using Parallels on a Mac so I can run this plug-in , but all my references are on local drives so I don't think this an issue, but I'll toss it out for comment anyway.
I am using Microsoft Excel 2016 MSO 32-bit and have done the following:
1. I have made c:\SMF add-in a trusted location
2. I have cleared the unblock tab on properties for C:\SMF Add-in\RCH_
My Excel is running on Windows using Parallels on a Mac so I can run this plug-in , but all my references are on local drives so I don't think this an issue, but I'll toss it out for comment anyway.
Fri Nov 10, 2017 2:04 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Sorry, but I haven't a clue as to what might be causing the crashes.
Can you create your own local add-in that automatically loads the add-in
when it starts up and then unloads the add-in as you shut it down?
On Fri, Nov 10, 2017 at 2:11 PM, dmohn1@
...
wrote:
> First, I love this app. Thank you Randy. I recently had to upgrade and
> reinstall courtesy of the Yahoo changes and now my Excel application
> crashes on startup and shutdown whenever the SMF add-in is loaded. If I
> start Excel without SMF add-in loaded, and then load the add-in, the SMF
> functions work beautifully and I can use SmfgetYahooPortfolioView without
> any problem, until I try to exit Excel and then Excel will crash.
>
> I am using Microsoft Excel 2016 MSO 32-bit and have done the following:
>
> 1. I have made c:\SMF add-in a trusted location
>
> 2. I have cleared the unblock tab on properties for C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla
>
> My Excel is running on Windows using Parallels on a Mac so I can run this
> plug-in , but all my references are on local drives so I don't think this
> an issue, but I'll toss it out for comment anyway.
>
Can you create your own local add-in that automatically loads the add-in
when it starts up and then unloads the add-in as you shut it down?
On Fri, Nov 10, 2017 at 2:11 PM, dmohn1@
...
wrote:
> First, I love this app. Thank you Randy. I recently had to upgrade and
> reinstall courtesy of the Yahoo changes and now my Excel application
> crashes on startup and shutdown whenever the SMF add-in is loaded. If I
> start Excel without SMF add-in loaded, and then load the add-in, the SMF
> functions work beautifully and I can use SmfgetYahooPortfoli
> any problem, until I try to exit Excel and then Excel will crash.
>
> I am using Microsoft Excel 2016 MSO 32-bit and have done the following:
>
> 1. I have made c:\SMF add-in a trusted location
>
> 2. I have cleared the unblock tab on properties for C:\SMF
> Add-in\RCH_
>
> My Excel is running on Windows using Parallels on a Mac so I can run this
> plug-in , but all my references are on local drives so I don't think this
> an issue, but I'll toss it out for comment anyway.
>
Fri Nov 10, 2017 1:59 pm (PST) . Posted by:
davie_001
Hi,
Thanks for the instant reply.
Yes, the template you cited is where I got the cell numbers for the reference row in my attached spreadsheet. Using those, and looking up how to get the substring Date for the Column C entries, I created the cell entries for the whole table, as given.
I downloaded my own attachment, fixed the links, and recalculated and all the right values populated the grid.
Since I want to have the sector weighting for a complete list of stocks, i.e. Column B, I tried to enter an array formula in Cell D4 after highlighting the range D4 to D20 and using the row titles D2 to D4.
Unlike your PotfolioView where the Category Titles are supplied immediately above the values grid, my Titles are manual and appear in Row 3. Would this positioning affect the grid results, or would it matter?
From the Cell contents in D4 of: =RCHGetElementNumber($B4,D$2)
I highlighted the range and manually typed in the following:
=RCHGetElementNumber($B$4:D$20,$D$2:$D$20) and hit CTRL-SHIFT-ENTER
Then I got every cell with an error in it (NUMBER?, I think), so maybe my table definition was wrong.
If the formula will accept the array range, what is the correct definition to type in?
And is there a way to copy a lengthy line in the cell without doing so one character at a time. Copying from Notepad doesn't seem to allow you to do the CTRL-SHIFT-ENTER, and a PASTE doesn't work.
Sorry for being so unaware of array entry, but this is pretty new to me, and handling multiple stocks is what I need.
I expect the substring Date would need a different array entry to work.
Thanks for the instant reply.
Yes, the template you cited is where I got the cell numbers for the reference row in my attached spreadsheet. Using those, and looking up how to get the substring Date for the Column C entries, I created the cell entries for the whole table, as given.
I downloaded my own attachment, fixed the links, and recalculated and all the right values populated the grid.
Since I want to have the sector weighting for a complete list of stocks, i.e. Column B, I tried to enter an array formula in Cell D4 after highlighting the range D4 to D20 and using the row titles D2 to D4.
Unlike your PotfolioView where the Category Titles are supplied immediately above the values grid, my Titles are manual and appear in Row 3. Would this positioning affect the grid results, or would it matter?
From the Cell contents in D4 of: =RCHGetElementNumbe
I highlighted the range and manually typed in the following:
=RCHGetElementNumbe
Then I got every cell with an error in it (NUMBER?, I think), so maybe my table definition was wrong.
If the formula will accept the array range, what is the correct definition to type in?
And is there a way to copy a lengthy line in the cell without doing so one character at a time. Copying from Notepad doesn't seem to allow you to do the CTRL-SHIFT-ENTER, and a PASTE doesn't work.
Sorry for being so unaware of array entry, but this is pretty new to me, and handling multiple stocks is what I need.
I expect the substring Date would need a different array entry to work.
Fri Nov 10, 2017 2:40 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The parameters for RCHGetElementNumber() are a ticker symbol and an
element number. It can't be array-entered and passed a range of cells for
either parameter. There wouldn't be any advantage to it. Even if I made it
capable of that, it would still need to go to individual web pages as
needed to get the data. And since array-entered formulas can cause
annoyances like not being able to sort within the range, or easily change
the range, I generally avoid them unless there is a reason to do so.
The reason smfGetYahooPortfolioView() and smfGetBarchartPortfolioView() are
more efficient when array-entered is that all of the data *can be*
retrieved in a single Internet access. If you fill 100 cells with one
array-entered formula, it accesses the Internet once. If you fill those 100
cells with individual formulas, they would access the Internet 100 times.
------------------------------
When I first looked at your attachment, I thought it would be good as an
smfUpdateDownloadTable macro application. Several reasons:
1. It's a simple table format.
2. It saves values instead of having formulas that recalculate every time
you open a workbook. I often like this aspect in my files.
3. You can selectively update rows (or columns, or ranges within the table)
4. Your data doesn't need to be updated every time you open the workbook.
The web pages aren't updated very often.
The table I refer to here
<https://wrongwaycoveredcalls.blogspot.com/2017/10/cc-list-candidates_31.html>
is such an application. I designed it so that all of the data on each line
(except for ex-dividend date) can be retrieved from a single Yahoo JSON
file. So it updates very quickly. I made the ex-dividend date process a
separate smfUpdateDownloadTable process, as each row really only needs to
be updated once a quarter (although I end up checking it more than once).
So I look up the ex-dividend dates from another worksheet in the workbook,
which looks like:
*Ticker* *TimeStamp* *GuruFocus Ex-Dividend Date* *Yahoo Ex-Dividend
Date* *Best
Ex-Dividend Date*
*SPHD* 2017-10-23 11:34 1899-12-30 2016-11-15 2017-11-15
*SPY* 2017-10-22 13:27 1899-12-30 2016-12-16 2017-12-16
*MMM* 2017-11-08 13:11 2017-08-23 2016-11-16 2017-11-16
*PEP* 2017-11-08 13:11 2017-08-30 2016-11-30 2017-11-30
*MRK* 2017-11-08 13:11 2017-09-14 2016-12-13 2017-12-13
*MO* 2017-11-08 13:11 2017-09-14 2016-12-20 2017-12-20
*PM* 2017-11-08 13:11 2017-09-26 2016-12-20 2017-12-20
The "TimeStamp" column tells me the last time I updated that row of data.
The GuruFocus ex-div date is what I retrieve from GuruFocus. Next time I am
checking data with that workbook, I'll probably just update the MMM and PEP
rows, to see if they have new ex-dividend dates (the worksheet is sorted by
ascending GF dates). The "Yahoo ex-dividend date" is last year's applicable
quarterly ex-dividend date from Yahoo historical quotes. The "Best" column
chooses which would be the best one to use for the upcoming quarterly
ex-dividend date.
------------------------------
*>>>*
And is there a way to copy a lengthy line in the cell without doing so one
character at a time. Copying from Notepad doesn't seem to allow you to do
the CTRL-SHIFT-ENTER, and a PASTE doesn't work.
Sorry, I'm not sure exactly what you're asking? I almost always do cut and
paste, but typically only on the text of the formula. I've found the
easiest way is to copy everything except the leading "=" sign. Then, either
go to the cell, or highlight the range, type "=" and paste the formula.
Then hit F2 if you need to do any editing on the formula before entering
it. Then either hit enter or Ctrl+Shift+Enter, depending on whether it's a
regular formula or array-entered formula.
------------------------------
On Fri, Nov 10, 2017 at 2:58 PM, dahook@
...
wrote:
>
> Thanks for the instant reply.
>
> Yes, the template you cited is where I got the cell numbers for the
> reference row in my attached spreadsheet. Using those, and looking up how
> to get the substring Date for the Column C entries, I created the cell
> entries for the whole table, as given.
>
> I downloaded my own attachment, fixed the links, and recalculated and all
> the right values populated the grid.
>
> Since I want to have the sector weighting for a complete list of stocks,
> i.e. Column B, I tried to enter an array formula in Cell D4 after
> highlighting the range D4 to D20 and using the row titles D2 to D4.
>
> Unlike your PotfolioView where the Category Titles are supplied
> immediately above the values grid, my Titles are manual and appear in Row
> 3. Would this positioning affect the grid results, or would it matter?
>
> From the Cell contents in D4 of: =RCHGetElementNumber($B4,D$2)
>
> I highlighted the range and manually typed in the following:
>
> =RCHGetElementNumber($B$4:D$20,$D$2:$D$20) and hit CTRL-SHIFT-ENTER
>
> Then I got every cell with an error in it (NUMBER?, I think), so maybe my
> table definition was wrong.
>
> If the formula will accept the array range, what is the correct definition
> to type in?
>
>
> And is there a way to copy a lengthy line in the cell without doing so one
> character at a time. Copying from Notepad doesn't seem to allow you to do
> the CTRL-SHIFT-ENTER, and a PASTE doesn't work.
>
> Sorry for being so unaware of array entry, but this is pretty new to me,
> and handling multiple stocks is what I need.
>
> I expect the substring Date would need a different array entry to work.
>
element number. It can't be array-entered and passed a range of cells for
either parameter. There wouldn't be any advantage to it. Even if I made it
capable of that, it would still need to go to individual web pages as
needed to get the data. And since array-entered formulas can cause
annoyances like not being able to sort within the range, or easily change
the range, I generally avoid them unless there is a reason to do so.
The reason smfGetYahooPortfolioView() and smfGetBarchartPortfolioView() are
more efficient when array-entered is that all of the data *can be*
retrieved in a single Internet access. If you fill 100 cells with one
array-entered formula, it accesses the Internet once. If you fill those 100
cells with individual formulas, they would access the Internet 100 times.
------------------------------
When I first looked at your attachment, I thought it would be good as an
smfUpdateDownloadTable macro application. Several reasons:
1. It's a simple table format.
2. It saves values instead of having formulas that recalculate every time
you open a workbook. I often like this aspect in my files.
3. You can selectively update rows (or columns, or ranges within the table)
4. Your data doesn't need to be updated every time you open the workbook.
The web pages aren't updated very often.
The table I refer to here
<https://wrongwaycoveredcalls.blogspot.com/2017/10/cc-list-candidates_31.html>
is such an application. I designed it so that all of the data on each line
(except for ex-dividend date) can be retrieved from a single Yahoo JSON
file. So it updates very quickly. I made the ex-dividend date process a
separate smfUpdateDownloadTa
be updated once a quarter (although I end up checking it more than once).
So I look up the ex-dividend dates from another worksheet in the workbook,
which looks like:
*Ticker* *TimeStamp* *GuruFocus Ex-Dividend Date* *Yahoo Ex-Dividend
Date* *Best
Ex-Dividend Date*
*SPHD* 2017-10-23 11:34 1899-12-30 2016-11-15 2017-11-15
*SPY* 2017-10-22 13:27 1899-12-30 2016-12-16 2017-12-16
*MMM* 2017-11-08 13:11 2017-08-23 2016-11-16 2017-11-16
*PEP* 2017-11-08 13:11 2017-08-30 2016-11-30 2017-11-30
*MRK* 2017-11-08 13:11 2017-09-14 2016-12-13 2017-12-13
*MO* 2017-11-08 13:11 2017-09-14 2016-12-20 2017-12-20
*PM* 2017-11-08 13:11 2017-09-26 2016-12-20 2017-12-20
The "TimeStamp&quo
The GuruFocus ex-div date is what I retrieve from GuruFocus. Next time I am
checking data with that workbook, I'll probably just update the MMM and PEP
rows, to see if they have new ex-dividend dates (the worksheet is sorted by
ascending GF dates). The "Yahoo ex-dividend date" is last year's applicable
quarterly ex-dividend date from Yahoo historical quotes. The "Best" column
chooses which would be the best one to use for the upcoming quarterly
ex-dividend date.
------------
*>>>
And is there a way to copy a lengthy line in the cell without doing so one
character at a time. Copying from Notepad doesn't seem to allow you to do
the CTRL-SHIFT-ENTER, and a PASTE doesn't work.
Sorry, I'm not sure exactly what you're asking? I almost always do cut and
paste, but typically only on the text of the formula. I've found the
easiest way is to copy everything except the leading "=" sign. Then, either
go to the cell, or highlight the range, type "=" and paste the formula.
Then hit F2 if you need to do any editing on the formula before entering
it. Then either hit enter or Ctrl+Shift+Enter, depending on whether it's a
regular formula or array-entered formula.
------------
On Fri, Nov 10, 2017 at 2:58 PM, dahook@
...
wrote:
>
> Thanks for the instant reply.
>
> Yes, the template you cited is where I got the cell numbers for the
> reference row in my attached spreadsheet. Using those, and looking up how
> to get the substring Date for the Column C entries, I created the cell
> entries for the whole table, as given.
>
> I downloaded my own attachment, fixed the links, and recalculated and all
> the right values populated the grid.
>
> Since I want to have the sector weighting for a complete list of stocks,
> i.e. Column B, I tried to enter an array formula in Cell D4 after
> highlighting the range D4 to D20 and using the row titles D2 to D4.
>
> Unlike your PotfolioView where the Category Titles are supplied
> immediately above the values grid, my Titles are manual and appear in Row
> 3. Would this positioning affect the grid results, or would it matter?
>
> From the Cell contents in D4 of: =RCHGetElementNumbe
>
> I highlighted the range and manually typed in the following:
>
> =RCHGetElementNumbe
>
> Then I got every cell with an error in it (NUMBER?, I think), so maybe my
> table definition was wrong.
>
> If the formula will accept the array range, what is the correct definition
> to type in?
>
>
> And is there a way to copy a lengthy line in the cell without doing so one
> character at a time. Copying from Notepad doesn't seem to allow you to do
> the CTRL-SHIFT-ENTER, and a PASTE doesn't work.
>
> Sorry for being so unaware of array entry, but this is pretty new to me,
> and handling multiple stocks is what I need.
>
> I expect the substring Date would need a different array entry to work.
>
Fri Nov 10, 2017 3:40 pm (PST) . Posted by:
davie_001
Thanks for explaining why the array entry wouldn't work in this case. Now that I've inserted the row references in the column headers instead of repeating the number in every cell, it already works much more smoothly, and it's easy to expand to add more securities.
I'm understanding better why the array method is useful, and if the PortfolioView and BarChart are the only ones that do benefit, then that too is good to know.
But, best of all you've clued me in to a real timesaver with the ability to copy paste and edit the array formula by leaving off the equal sign until the editing is done. It was excruciating to type long sequences one character at a time, especially if I needed to add the path to the SMF folder In the entry.
If you do proceed with adapting my Sector table to a macro, I'd appreciate knowing how I could add the date of last fetching to it, as the contents of different securities vary between iShares, BMO, Vanguard and I never know which ones have actually changed from one load to the next.
I'm working on cleaning up my table of geographical breakdowns for ETFs and mutual funds from Morningstar which use the massively complex
=RCHGetTableCell( URL, Cell#, [Find1], [Find2], [Find3], [Find4], [Row#], [EndMarker], [Look#], [ErrorMsg], [Type] )
The URL contains a "t=FIG" or similar symbol reference embedded, and I'm not sure how to replace that with a cell reference to the column heading, so I can make a table like:
MAW104 MAW105 RBF1010 FIG As of 30/06/2017 As of 30/06/2017 As of 31/07/2017 As of 31/07/2017 Fixed Income 32.85 31.75 97.78 95.94 Canadian Equity 16.88 16.68 0.00 0.00 U.S. Equity 21.26 20.75 0.00 0.00 International Equity 22.61 21.41 0.00 0.00 Other 0.32 0.33 0.09 -0.98 Cash 6.08 9.08 2.13 5.05 Total 100.00 100.00 100.00 100.01
I'm understanding better why the array method is useful, and if the PortfolioView and BarChart are the only ones that do benefit, then that too is good to know.
But, best of all you've clued me in to a real timesaver with the ability to copy paste and edit the array formula by leaving off the equal sign until the editing is done. It was excruciating to type long sequences one character at a time, especially if I needed to add the path to the SMF folder In the entry.
If you do proceed with adapting my Sector table to a macro, I'd appreciate knowing how I could add the date of last fetching to it, as the contents of different securities vary between iShares, BMO, Vanguard and I never know which ones have actually changed from one load to the next.
I'm working on cleaning up my table of geographical breakdowns for ETFs and mutual funds from Morningstar which use the massively complex
=RCHGetTableCell( URL, Cell#, [Find1], [Find2], [Find3], [Find4], [Row#], [EndMarker], [Look#], [ErrorMsg], [Type] )
The URL contains a "t=FIG" or similar symbol reference embedded, and I'm not sure how to replace that with a cell reference to the column heading, so I can make a table like:
MAW104 MAW105 RBF1010 FIG As of 30/06/2017 As of 30/06/2017 As of 31/07/2017 As of 31/07/2017 Fixed Income 32.85 31.75 97.78 95.94 Canadian Equity 16.88 16.68 0.00 0.00 U.S. Equity 21.26 20.75 0.00 0.00 International Equity 22.61 21.41 0.00 0.00 Other 0.32 0.33 0.09 -0.98 Cash 6.08 9.08 2.13 5.05 Total 100.00 100.00 100.00 100.01
Fri Nov 10, 2017 4:21 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm concerned by your comment of "especially if I needed to add the path to
the SMF folder In the entry". You should never be doing that. If you have
formulas with a location in front of the function, it means they are NOT
using the installed add-in. And if they are working, it means you have the
add-in located in more than one location. What happens is that whenever an
EXCEL workbook is saved, it saves a hard-coded location of the add-in to
every function that is from the add-in. Later, when EXCEL next opens that
workbook, it verifies that hard-coded location. If it finds that location
is of an installed add-in, it removes the location from the formula. If it
finds that location is not for an installed add-in, it checks to see if the
link is valid. If it is, the formula should work. If not valid, it tags it
as an "unresolved link" and won't recalculate it. The easiest way to fix
unresolved links is to run the smfFixLinks macro.
Re: "
If you do proceed with adapting my Sector table to a macro". That was
something I was suggesting you do. See the FAQs web page for info on the
smfUpdateDownloadTable macro. The "Timestamp" column is simply a column
formula definition of "NOW()".
RCHGetTableCell() complex? Nah. 99% of my usage is only the first three
parameters. Can you give me an example of the formula you're using for FIG?
I don't see such geographical breakdowns for it on Morningstar. But it's
just normal EXCEL concatenation to get a ticker value from a cell into a
URL.
On Fri, Nov 10, 2017 at 4:40 PM, dahook@
...
wrote:
>
> Thanks for explaining why the array entry wouldn't work in this case. Now
> that I've inserted the row references in the column headers instead of
> repeating the number in every cell, it already works much more smoothly,
> and it's easy to expand to add more securities.
>
> I'm understanding better why the array method is useful, and if the
> PortfolioView and BarChart are the only ones that do benefit, then that too
> is good to know.
>
> But, best of all you've clued me in to a real timesaver with the ability
> to copy paste and edit the array formula by leaving off the equal sign
> until the editing is done. It was excruciating to type long sequences one
> character at a time, especially if I needed to add the path to the SMF
> folder In the entry.
>
>
> If you do proceed with adapting my Sector table to a macro, I'd appreciate
> knowing how I could add the date of last fetching to it, as the contents of
> different securities vary between iShares, BMO, Vanguard and I never know
> which ones have actually changed from one load to the next.
>
> I'm working on cleaning up my table of geographical breakdowns for ETFs
> and mutual funds from Morningstar which use the massively complex
>
> =RCHGetTableCell( URL, Cell#, [Find1], [Find2], [Find3], [Find4], [Row#],
> [EndMarker], [Look#], [ErrorMsg], [Type] )
>
> The URL contains a "t=FIG" or similar symbol reference embedded, and I'm
> not sure how to replace that with a cell reference to the column heading,
> so I can make a table like:
>
> *MAW104* *MAW105* *RBF1010* *FIG*
> *As of 30/06/2017* *As of 30/06/2017* *As of 31/07/2017* *As of
> 31/07/2017*
> *Fixed Income* 32.85 31.75 97.78 95.94
> *Canadian Equity* 16.88 16.68 0.00 0.00
> *U.S. Equity* 21.26 20.75 0.00 0.00
> *International Equity* 22.61 21.41 0.00 0.00
> *Other* 0.32 0.33 0.09 -0.98
> *Cash* 6.08 9.08 2.13 5.05
> *Total* *100.00* *100.00* *100.00* *100.01*
>
>
>
>
the SMF folder In the entry". You should never be doing that. If you have
formulas with a location in front of the function, it means they are NOT
using the installed add-in. And if they are working, it means you have the
add-in located in more than one location. What happens is that whenever an
EXCEL workbook is saved, it saves a hard-coded location of the add-in to
every function that is from the add-in. Later, when EXCEL next opens that
workbook, it verifies that hard-coded location. If it finds that location
is of an installed add-in, it removes the location from the formula. If it
finds that location is not for an installed add-in, it checks to see if the
link is valid. If it is, the formula should work. If not valid, it tags it
as an "unresolved link" and won't recalculate it. The easiest way to fix
unresolved links is to run the smfFixLinks macro.
Re: "
If you do proceed with adapting my Sector table to a macro". That was
something I was suggesting you do. See the FAQs web page for info on the
smfUpdateDownloadTa
formula definition of "NOW()"
RCHGetTableCell(
parameters. Can you give me an example of the formula you're using for FIG?
I don't see such geographical breakdowns for it on Morningstar. But it's
just normal EXCEL concatenation to get a ticker value from a cell into a
URL.
On Fri, Nov 10, 2017 at 4:40 PM, dahook@
...
wrote:
>
> Thanks for explaining why the array entry wouldn't work in this case. Now
> that I've inserted the row references in the column headers instead of
> repeating the number in every cell, it already works much more smoothly,
> and it's easy to expand to add more securities.
>
> I'm understanding better why the array method is useful, and if the
> PortfolioView and BarChart are the only ones that do benefit, then that too
> is good to know.
>
> But, best of all you've clued me in to a real timesaver with the ability
> to copy paste and edit the array formula by leaving off the equal sign
> until the editing is done. It was excruciating to type long sequences one
> character at a time, especially if I needed to add the path to the SMF
> folder In the entry.
>
>
> If you do proceed with adapting my Sector table to a macro, I'd appreciate
> knowing how I could add the date of last fetching to it, as the contents of
> different securities vary between iShares, BMO, Vanguard and I never know
> which ones have actually changed from one load to the next.
>
> I'm working on cleaning up my table of geographical breakdowns for ETFs
> and mutual funds from Morningstar which use the massively complex
>
> =RCHGetTableCell( URL, Cell#, [Find1], [Find2], [Find3], [Find4], [Row#],
> [EndMarker], [Look#], [ErrorMsg], [Type] )
>
> The URL contains a "t=FIG" or similar symbol reference embedded, and I'm
> not sure how to replace that with a cell reference to the column heading,
> so I can make a table like:
>
> *MAW104* *MAW105* *RBF1010* *FIG*
> *As of 30/06/2017* *As of 30/06/2017* *As of 31/07/2017* *As of
> 31/07/2017*
> *Fixed Income* 32.85 31.75 97.78 95.94
> *Canadian Equity* 16.88 16.68 0.00 0.00
> *U.S. Equity* 21.26 20.75 0.00 0.00
> *International Equity* 22.61 21.41 0.00 0.00
> *Other* 0.32 0.33 0.09 -0.98
> *Cash* 6.08 9.08 2.13 5.05
> *Total* *100.00* *100.00* *100.00* *100.01*
>
>
>
>
Fri Nov 10, 2017 1:59 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's *smf*GetYahooPortfolioView(), not *rch*GetYahooPortfolioView().
The original functions started with the "rch" prefix, just so I could have
them grouped separately. Shortly after I started sharing them as an add-in,
I started to use the "smf" prefix instead. But I couldn't just change the
original functions, because others were already using them. :(
On Fri, Nov 10, 2017 at 2:26 PM, cancerfixer@
...
wrote:
>
> This is curious. I hope someone can help. I have installed the latest
> add-in (2.1.2017.11.08) and confirmed it is installed with
>
> =RCHGetElementNumber("Version")
>
> which returns the correct information.
>
> I can run a procedure that accumulates tickers in column A5:A204 and
> writes individual info desired by 2 digit number in row 4 (B4:AD4)
> (formatted as text, not numeric). The array entered formula is then
> written by the macro to cover the entire range with
>
> =rchGetYahooPortfolioView($A$5:$A$204,$B$4:$AD$4,,0)
>
> All I get is #NAME? in every cell. Now I know this means excel is not
> finding the function, yet if I copy the SAME information over to the
> example spreadsheet (using copy-> paste values) in the appropriate places,
> the function works as designed. And RCHGet ElementNumber("Version")
> returns the correct version that is installed on my computer in the correct
> place.
>
> I've forced recalculation, but this changes nothing, nor would I expect it
> to.
>
> I'll try to attach a screen shot but am not sure how to do this, and you
> should get the idea from above. So why would it NOT work on my
> spreadsheet, yet work OK on Randy's example spreadsheet? I suppose I've
> done something stupid, but I'm stumped. Any trouble-shooting ideas
> appreciated.
>
>
>
The original functions started with the "rch" prefix, just so I could have
them grouped separately. Shortly after I started sharing them as an add-in,
I started to use the "smf" prefix instead. But I couldn't just change the
original functions, because others were already using them. :(
On Fri, Nov 10, 2017 at 2:26 PM, cancerfixer@
...
wrote:
>
> This is curious. I hope someone can help. I have installed the latest
> add-in (2.1.2017.11.
>
> =RCHGetElementNumbe
>
> which returns the correct information.
>
> I can run a procedure that accumulates tickers in column A5:A204 and
> writes individual info desired by 2 digit number in row 4 (B4:AD4)
> (formatted as text, not numeric). The array entered formula is then
> written by the macro to cover the entire range with
>
> =rchGetYahooPortfol
>
> All I get is #NAME? in every cell. Now I know this means excel is not
> finding the function, yet if I copy the SAME information over to the
> example spreadsheet (using copy-> paste values) in the appropriate places,
> the function works as designed. And RCHGet ElementNumber(
> returns the correct version that is installed on my computer in the correct
> place.
>
> I've forced recalculation, but this changes nothing, nor would I expect it
> to.
>
> I'll try to attach a screen shot but am not sure how to do this, and you
> should get the idea from above. So why would it NOT work on my
> spreadsheet, yet work OK on Randy's example spreadsheet? I suppose I've
> done something stupid, but I'm stumped. Any trouble-shooting ideas
> appreciated.
>
>
>
Fri Nov 10, 2017 2:16 pm (PST) . Posted by:
pauljl52
Thanks again for your efforts.
If I need the information available from the smfGetBarchartPortfolioView I can run it on my Win7 IE11 set up.
294 Fields of Data Amazing! Also provides a workaround in case Yahoo shuts down access via smfGetYahooPortfolioView.
If I need the information available from the smfGetBarchartPortf
294 Fields of Data Amazing! Also provides a workaround in case Yahoo shuts down access via smfGetYahooPortfoli
Fri Nov 10, 2017 2:56 pm (PST) . Posted by:
borat_rules
Hey Randy,
You were totally right, I had basically created an array in the one cell and copied it to the others but once I highlighted all the cells and created the Array it worked like a charm.
So for anyone else that is getting "--" when they create an array in a single cell and copy it to the rest, the solution is to highlight all the cells then create the formula in the formula bar and hit ctrl+shift+enter.
thanks again for your help.
SH
You were totally right, I had basically created an array in the one cell and copied it to the others but once I highlighted all the cells and created the Array it worked like a charm.
So for anyone else that is getting "--" when they create an array in a single cell and copy it to the rest, the solution is to highlight all the cells then create the formula in the formula bar and hit ctrl+shift+enter.
thanks again for your help.
SH
Fri Nov 10, 2017 3:42 pm (PST) . Posted by:
"Ron Spruell" hashky
Randy -
Would you break down the finviz example?
Can the tickers be array entered?
My desire is to pull in 10 to 15 data points on 15 stocks. What would be a good way to do it. I am in the process of setting up portfolio view.
Ron
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, November 9, 2017 9:59 AM
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&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.
#yiv1592416424 #yiv1592416424 -- #yiv1592416424ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1592416424 #yiv1592416424ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1592416424 #yiv1592416424ygrp-mkp #yiv1592416424hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1592416424 #yiv1592416424ygrp-mkp #yiv1592416424ads {margin-bottom:10px;}#yiv1592416424 #yiv1592416424ygrp-mkp .yiv1592416424ad {padding:0 0;}#yiv1592416424 #yiv1592416424ygrp-mkp .yiv1592416424ad p {margin:0;}#yiv1592416424 #yiv1592416424ygrp-mkp .yiv1592416424ad a {color:#0000ff;text-decoration:none;}#yiv1592416424 #yiv1592416424ygrp-sponsor #yiv1592416424ygrp-lc {font-family:Arial;}#yiv1592416424 #yiv1592416424ygrp-sponsor #yiv1592416424ygrp-lc #yiv1592416424hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1592416424 #yiv1592416424ygrp-sponsor #yiv1592416424ygrp-lc .yiv1592416424ad {margin-bottom:10px;padding:0 0;}#yiv1592416424 #yiv1592416424actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1592416424 #yiv1592416424activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1592416424 #yiv1592416424activity span {font-weight:700;}#yiv1592416424 #yiv1592416424activity span:first-child {text-transform:uppercase;}#yiv1592416424 #yiv1592416424activity span a {color:#5085b6;text-decoration:none;}#yiv1592416424 #yiv1592416424activity span span {color:#ff7900;}#yiv1592416424 #yiv1592416424activity span .yiv1592416424underline {text-decoration:underline;}#yiv1592416424 .yiv1592416424attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1592416424 .yiv1592416424attach div a {text-decoration:none;}#yiv1592416424 .yiv1592416424attach img {border:none;padding-right:5px;}#yiv1592416424 .yiv1592416424attach label {display:block;margin-bottom:5px;}#yiv1592416424 .yiv1592416424attach label a {text-decoration:none;}#yiv1592416424 blockquote {margin:0 0 0 4px;}#yiv1592416424 .yiv1592416424bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1592416424 .yiv1592416424bold a {text-decoration:none;}#yiv1592416424 dd.yiv1592416424last p a {font-family:Verdana;font-weight:700;}#yiv1592416424 dd.yiv1592416424last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1592416424 dd.yiv1592416424last p span.yiv1592416424yshortcuts {margin-right:0;}#yiv1592416424 div.yiv1592416424attach-table div div a {text-decoration:none;}#yiv1592416424 div.yiv1592416424attach-table {width:400px;}#yiv1592416424 div.yiv1592416424file-title a, #yiv1592416424 div.yiv1592416424file-title a:active, #yiv1592416424 div.yiv1592416424file-title a:hover, #yiv1592416424 div.yiv1592416424file-title a:visited {text-decoration:none;}#yiv1592416424 div.yiv1592416424photo-title a, #yiv1592416424 div.yiv1592416424photo-title a:active, #yiv1592416424 div.yiv1592416424photo-title a:hover, #yiv1592416424 div.yiv1592416424photo-title a:visited {text-decoration:none;}#yiv1592416424 div#yiv1592416424ygrp-mlmsg #yiv1592416424ygrp-msg p a span.yiv1592416424yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1592416424 .yiv1592416424green {color:#628c2a;}#yiv1592416424 .yiv1592416424MsoNormal {margin:0 0 0 0;}#yiv1592416424 o {font-size:0;}#yiv1592416424 #yiv1592416424photos div {float:left;width:72px;}#yiv1592416424 #yiv1592416424photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1592416424 #yiv1592416424photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1592416424 #yiv1592416424reco-category {font-size:77%;}#yiv1592416424 #yiv1592416424reco-desc {font-size:77%;}#yiv1592416424 .yiv1592416424replbq {margin:4px;}#yiv1592416424 #yiv1592416424ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1592416424 #yiv1592416424ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1592416424 #yiv1592416424ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1592416424 #yiv1592416424ygrp-mlmsg select, #yiv1592416424 input, #yiv1592416424 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1592416424 #yiv1592416424ygrp-mlmsg pre, #yiv1592416424 code {font:115% monospace;}#yiv1592416424 #yiv1592416424ygrp-mlmsg * {line-height:1.22em;}#yiv1592416424 #yiv1592416424ygrp-mlmsg #yiv1592416424logo {padding-bottom:10px;}#yiv1592416424 #yiv1592416424ygrp-msg p a {font-family:Verdana;}#yiv1592416424 #yiv1592416424ygrp-msg p#yiv1592416424attach-count span {color:#1E66AE;font-weight:700;}#yiv1592416424 #yiv1592416424ygrp-reco #yiv1592416424reco-head {color:#ff7900;font-weight:700;}#yiv1592416424 #yiv1592416424ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1592416424 #yiv1592416424ygrp-sponsor #yiv1592416424ov li a {font-size:130%;text-decoration:none;}#yiv1592416424 #yiv1592416424ygrp-sponsor #yiv1592416424ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1592416424 #yiv1592416424ygrp-sponsor #yiv1592416424ov ul {margin:0;padding:0 0 0 8px;}#yiv1592416424 #yiv1592416424ygrp-text {font-family:Georgia;}#yiv1592416424 #yiv1592416424ygrp-text p {margin:0 0 1em 0;}#yiv1592416424 #yiv1592416424ygrp-text tt {font-size:120%;}#yiv1592416424 #yiv1592416424ygrp-vital ul li:last-child {border-right:none !important;}#yiv1592416424
Would you break down the finviz example?
Can the tickers be array entered?
My desire is to pull in 10 to 15 data points on 15 stocks. What would be a good way to do it. I am in the process of setting up portfolio view.
Ron
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, November 9, 2017 9:59 AM
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&p=d&t=MMM,IBM","td",1,"?t=MMM&",">Price",,,1)
=smfGetTagContent("https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM","
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 smfGetFinvizPortfol
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.
#yiv1592416424 #yiv1592416424 -- #yiv1592416424ygrp-
Fri Nov 10, 2017 4:01 pm (PST) . Posted by:
peterebali
Randy, A FinVIZPOrtfolioView would be useful as it has some fields that are useful that are currently not available from YahooPortfolioView
ATR - for setting stop losses
RSI - for BUY / SELL signals
Cheers...
Peter
ATR - for setting stop losses
RSI - for BUY / SELL signals
Cheers...
Peter
Fri Nov 10, 2017 3:55 pm (PST) . Posted by:
zbgb952
Hello Randy,
What is the smf formula to use to get the price, say, at the link shown below for JNJ?
https://finviz.com/quote.ashx?t=jnj https://finviz.com/quote.ashx?t=jnj
The price field is shown right below the 'previous close' field.
I ask this because the Yahoo function no longer works as you know. Thanks.
ZGB
What is the smf formula to use to get the price, say, at the link shown below for JNJ?
https://finviz.com/quote.ashx?t=jnj https://finviz.com/quote.ashx?t=jnj
The price field is shown right below the 'previous close' field.
I ask this because the Yahoo function no longer works as you know. Thanks.
ZGB
Fri Nov 10, 2017 4:24 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Asked and answered several days ago?
On Fri, Nov 10, 2017 at 4:55 PM, mb0329@
...
wrote:
>
> What is the smf formula to use to get the price, say, at the link shown
> below for JNJ?
>
> https://finviz.com/quote.ashx?t=jnj
>
> The price field is shown right below the 'previous close' field.
>
> I ask this because the Yahoo function no longer works as you know. Thanks.
>
>
On Fri, Nov 10, 2017 at 4:55 PM, mb0329@
...
wrote:
>
> What is the smf formula to use to get the price, say, at the link shown
> below for JNJ?
>
> https://finviz.com/quote.ashx?t=jnj
>
> The price field is shown right below the 'previous close' field.
>
> I ask this because the Yahoo function no longer works as you know. Thanks.
>
>
Fri Nov 10, 2017 4:31 pm (PST) . Posted by:
thomas.strouse@nielsen.com
Randy,
If I buy the premium package from Morningstar to gain access to their 10-year data, is it correct that I will be able to access that 10-year data from the existing SMF functions?
Thanks
Tom
If I buy the premium package from Morningstar to gain access to their 10-year data, is it correct that I will be able to access that 10-year data from the existing SMF functions?
Thanks
Tom
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar