Sabtu, 25 Oktober 2014

[smf_addin] Digest Number 3221

15 Messages

Digest #3221
1b
Re: Historical data of market corrections by "Randy Harmelink" rharmelink
1d
Re: Historical data of market corrections by "Randy Harmelink" rharmelink
5b
Re: Distribute Spreadsheet with SMF Add-In by "Randy Harmelink" rharmelink

Messages

Sat Oct 25, 2014 8:18 am (PDT) . Posted by:

steven_rourk

something is not working correctly because it is not stopping in the first row where there is data en columnA, its giving me numbers that are past that row.

Sat Oct 25, 2014 8:54 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

If you want it to stop at the first row in column A, you'll need MIN() on
the A row instead of MAX()...

Unfortunately, for the data you gave as an example, only one amout was
greater than 0. So it would be both the MIN() and the MAX() greater than
zero.

On Sat, Oct 25, 2014 at 8:18 AM, steven_rourk@...wrote:

>
> something is not working correctly because it is not stopping in the first
> row where there is data en columnA, its giving me numbers that are past
> that row.
>

Sat Oct 25, 2014 10:55 am (PDT) . Posted by:

steven_rourk

ok, my fault for not giving you the real data.... let me give your the real data, with the real columns:




I
K
M
N
2599
0
0
1144.06
1084.1
2600
0
0
1144.06
1084.1
2601
0
0
1144.06
1084.1
2602
0
0
1144.06
1084.1
2603
0
0
1144.06
1084.1
2604
1144.06
0
0
1084.1
2605
0
0
0
1084.1
2606
0
0
0
1084.1
2607
0
0
#REF!
1084.1
2608
0
1088.68
0
1084.1
2609
0
0
#REF!
1084.1
2610
0
1084.1
0
1087.12
2611
0
0
0
1087.12
2612
0
0
0
1087.12
2613
0
0
0
1087.12
2614
0
0
#REF!
1087.12
2615
0
1087.12
1150.57
#VALUE!
2616
0
0
1150.57
#VALUE!
2617
1150.57
0
0
1063.23







COLUMN I: is data


COLUMN K: is data


COLUMN M: in row 2599 I got the following array entered formula:
=MAX(OFFSET(I2600,0,0,MIN(IF(K2600:K2900>0,ROW(K2600:K2900),99999))-ROW(K2600),1))


I got no problem with the error in column M... I can live with that, because in the rest of the cells its giving me the correct results. For example in M2599 it is correctly giving me 1144.06 that is the maximum value in COLUMN I between the next row 2600 and the first row where there is data in COLUMN K (the yellow selection)





COLUMN N: in row 2599 I got the following array entered formula:
=SMALL(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),COUNTIF(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),0)+1)


In N2599 it is giving me incorrectly 1084.1 as the minimum value in COLUMN K between the next row 2600 and the first row where there is data in COLUMN I (the orange selection).... In this case as there is no data I need it to be 0 or error...But in N2604 the result 1084.1 is correct as this is the minimum value of the blue seccion.


In other words, when there is no data, or there is only ceros, it should give cero or error... and if there is data it should give me the minimum value of that set of data







Sat Oct 25, 2014 6:11 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Since your formulas exceed the size of the given data, I can't fill it in
for testing.

It would be easier if you would attach a file.

Earlier, I mentioned point & figure. I've uploaded the file I was working
on 7 years ago to the SMF web site:

http://ogres-crypt.com/SMF/Misc/Point-and-Figure-Work-in-Progress.xls

It's doing a trend and reversal type analysis, but based on the number of
boxes and box size, instead of a straight percentage.

On Sat, Oct 25, 2014 at 10:55 AM, steven_rourk@...wrote:

>
> ok, my fault for not giving you the real data.... let me give your the
> real data, with the real columns:
>
>
>
>
> I
>
> K
>
> M
>
> N
>
> 2599
>
> 0
>
> 0
>
> 1144.06
>
> 1084.1
>
> 2600
>
> 0
>
> 0
>
> 1144.06
>
> 1084.1
>
> 2601
>
> 0
>
> 0
>
> 1144.06
>
> 1084.1
>
> 2602
>
> 0
>
> 0
>
> 1144.06
>
> 1084.1
>
> 2603
>
> 0
>
> 0
>
> 1144.06
>
> 1084.1
>
> 2604
>
> 1144.06
>
> 0
>
> 0
>
> 1084.1
>
> 2605
>
> 0
>
> 0
>
> 0
>
> 1084.1
>
> 2606
>
> 0
>
> 0
>
> 0
>
> 1084.1
>
> 2607
>
> 0
>
> 0
>
> #REF!
>
> 1084.1
>
> 2608
>
> 0
>
> 1088.68
>
> 0
>
> 1084.1
>
> 2609
>
> 0
>
> 0
>
> #REF!
>
> 1084.1
>
> 2610
>
> 0
>
> 1084.1
>
> 0
>
> 1087.12
>
> 2611
>
> 0
>
> 0
>
> 0
>
> 1087.12
>
> 2612
>
> 0
>
> 0
>
> 0
>
> 1087.12
>
> 2613
>
> 0
>
> 0
>
> 0
>
> 1087.12
>
> 2614
>
> 0
>
> 0
>
> #REF!
>
> 1087.12
>
> 2615
>
> 0
>
> 1087.12
>
> 1150.57
>
> #VALUE!
>
> 2616
>
> 0
>
> 0
>
> 1150.57
>
> #VALUE!
>
> 2617
>
> 1150.57
>
> 0
>
> 0
>
> 1063.23
>
>
>
> COLUMN I: is data
>
> COLUMN K: is data
>
> COLUMN M: in row 2599 I got the following array entered formula:
>
> =MAX(OFFSET(I2600,0,0,MIN(IF(K2600:K2900>0,ROW(K2600:K2900),99999))-ROW(K2600),1))
>
> I got no problem with the error in column M... I can live with that,
> because in the rest of the cells its giving me the correct results. For
> example in M2599 it is correctly giving me 1144.06 that is the maximum
> value in COLUMN I between the next row 2600 and the first row where there
> is data in COLUMN K (the yellow selection)
>
>
> COLUMN N: in row 2599 I got the following array entered formula:
>
> =SMALL(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),COUNTIF(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),0)+1)
>
> In N2599 it is giving me incorrectly 1084.1 as the minimum value in COLUMN
> K between the next row 2600 and the first row where there is data in COLUMN
> I (the orange selection).... In this case as there is no data I need it to
> be 0 or error...But in N2604 the result 1084.1 is correct as this is the
> minimum value of the blue seccion.
>
> In other words, when there is no data, or there is only ceros, it should
> give cero or error... and if there is data it should give me the minimum
> value of that set of data
>
>
>
>
>
>
>
>

Sat Oct 25, 2014 1:45 pm (PDT) . Posted by:

bob_15861586

Randy,

It is now working for me. Thank you very much.


Bob

Sat Oct 25, 2014 2:36 pm (PDT) . Posted by:

blm3@sbcglobal.net

I would typically be running my sheet after market close.

Thanks and regards


Sat Oct 25, 2014 5:34 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You should use the RCHGetYahooQuotes() function for closing quotes. It can
get data on up to 200 ticker symbols per invocation.

I don't know of a source for alpha, which could have any number of
definitions.

On Sat, Oct 25, 2014 at 2:36 PM, blm3@... wrote:

>
> I would typically be running my sheet after market close.
>
>

Sat Oct 25, 2014 7:38 pm (PDT) . Posted by:

tzewei_79

Hi Randy,

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"&addticker=") where A12 has the ticker works. I'm rying to understand the example at http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html
, but I don't understand what does "h3", "-1" and &addticker=" means.


The syntax shows:
=smfGetTagContent( URL, Tag, Tag#, [Find1], [Find2], [Find3], [Find4], [Convert], [ErrorMsg], [Type] )



Parameter is:
Parameters

URL = Web page to retrieve the HTML tag content from.

Tag = The type of tag to retrieve content from -- "table", "td", "li", "span", etc.

Tag# = The number of tags to skip backward or forward, after function is positioned on the page by "Find1" thru "Find4", before returning data.

Find1 = An optional string value to search for to position the function on the page before skipping backward or forward to find the data to return. Defaults to "<", which should position you at the top of the document.

Find2 = An optional string value to search for to further position the function on the page (after finding the "Find1" string) before skipping backword or forward to find the data to return. Defaults to " ".

Find3 = An optional string value to search for to further position the function on the page (after finding the "Find1" thru "Find2" strings) before skipping backward or forward to find the data to return. Defaults to " ".

Find4 = An optional string value to search for to further position the function on the page (after finding the "Find1" thru "Find3" strings) before skipping backward or forward to find the data to return. Defaults to " ".

Convert = An indicator of whether the data found within the tag should be converted or parsed. Unused at this time. Defaults to 0.

ErrorMsg = An optional value to return if the table cell cannot be found based on specified parameters. Defaults to "Error".

Type = An optional integer value to determine the type of Internet request to make. Defaults to 0. For now, other values are experimental:

0 = XMLHTTP "Get" Request
1 = IE Object Request
2 = HTMLDocument Request
3 = XMLHTTP "Post" Request


What does "h3" tag means? Is this the "header3" html tag? How do we know it is h3 tag from the page? When I inspect the element, I can't relate h3 to CCRC or ticker.
Does -1 means to look backwards after finding the tag?
How did you get the &addticker from the google page? Do you check the source from "inspect element"? They are many folders there and I can only find addticker inside a href.


I'm sorry I always had to ask so many questions before I can't understand the meaning of the syntax examples...




Sat Oct 25, 2014 7:58 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You need to look at the source code of the web page in order to determine
what would be the best combination of search strings. Best if they are
unique on the web page.

In this case, I found this HTML code:

<div class="g-unit g-first"><h3>The Allstate
Corporation  </h3>(Public, NYSE:ALL)  
<a
href="/finance/portfolio?action=add&addticker=NYSE%3AALL&ei=XmBMVPDmF6mRiAL7q4GACQ"
class=norm>Watch this stock</a>
 
</div><div class="g-unit norm">Find more results for<b>

The add-in converts all of the HTML encoding (i.e.   and &) into
their normal characters. This is the first occurrence of "&addticker=" on
the web page, and it is fairly unique and stable, so I chose to use it.

The tag that I want to extract data from is the "<h3>...</h3>" tag that
contains the company name.

I use the -1 because I want it to find the first "<h3>" BEFORE where it
finds the first "&addticker=" string.

Note there are other possible search strings there:

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,">Watch
this stock")
=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,">Find
more results")

On Sat, Oct 25, 2014 at 7:38 PM, tzewei_79@yahoo.com.sg [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> =smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"&addticker=")
> where A12 has the ticker works. I'm rying to understand the example at
> http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html
>
> , but I don't understand what does "h3", "-1" and &addticker=" means.
>
> What does "h3" tag means? Is this the "header3" html tag? How do we know
> it is h3 tag from the page? When I inspect the element, I can't relate h3
> to CCRC or ticker.
> Does -1 means to look backwards after finding the tag?
> How did you get the &addticker from the google page? Do you check the
> source from "inspect element"? They are many folders there and I can only
> find addticker inside a href.
>
> I'm sorry I always had to ask so many questions before I can't understand
> the meaning of the syntax examples...
>

Sat Oct 25, 2014 8:25 pm (PDT) . Posted by:

tzewei_79

Hi Randy,

I don't quite understand the &addticker but I think I understood the other 2 examples you quoted.


It's like I can also use this to extract right?


=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"(Public")



It seems that I will need to use any text that has a ">" in front? The others will just return error in the cell?

Sat Oct 25, 2014 9:20 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You can actually use any text in your search string. I just use the ">"
regularly, because it can make the string more unique. For example, some
people might use "beta" as a search term, as the label in front of a beta
value they want to pull. But if the META tag on the web page uses the word
beta in it, or if beta happens to be in some descriptive text, then "beta"
search string would find the wrong string. But ">Beta", IF APPLICABLE,
might make the string unique.

On Sat, Oct 25, 2014 at 8:25 PM, tzewei_79@yahoo.com.sg [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I don't quite understand the &addticker but I think I understood the other
> 2 examples you quoted.
>
> It's like I can also use this to extract right?
>
> =smfGetTagContent("https://www.google.com/finance?q=
> "&A12,"h3",-1,"(Public")
>
> It seems that I will need to use any text that has a ">" in front? The
> others will just return error in the cell?
>
>
> __._,_._
>

Sat Oct 25, 2014 8:01 pm (PDT) . Posted by:

tzewei_79

Hi Randy,

I tried to replace C:\SMF Add-in\RCH_Stock_Market_Functions.xla! with nothing but Excel 2010 says Excel cannot find any data to replace.


How do I add the smfFixLinks function into excel? I opened vb in excel, insert module under Modules folder and added before saving it. The cells still shows #NAME!.


Public Sub smfFixLinks()
ActiveSheet.Cells.Replace _
What:="'C:&#92;SMF Add-in\RCH_Stock_Market_Functions.xla'!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub





Sat Oct 25, 2014 8:15 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

What you use in the "Find and Replace All" will depend on what prefix is in
your cells. The string I gave in the email was just giving an example of
what it might look like. Yours may not be the same.

An improved version of the smfFixLinks macro is already within the add-in
and available to use. I have it assigned to a toolbar button so I have easy
access to it.

On Sat, Oct 25, 2014 at 8:01 PM, tzewei_79@yahoo.com.sg [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I tried to replace C:\SMF Add-in\RCH_Stock_Market_Functions.xla! with
> nothing but Excel 2010 says Excel cannot find any data to replace.
>
> How do I add the smfFixLinks function into excel? I opened vb in excel,
> insert module under Modules folder and added before saving it. The cells
> still shows #NAME!.
>
> Public Sub smfFixLinks()
> ActiveSheet.Cells.Replace _
> What:="'C:&#92;SMF Add-in\RCH_Stock_Market_Functions.xla'!", _
> Replacement:="", _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
> End Sub
>
>

Sat Oct 25, 2014 8:45 pm (PDT) . Posted by:

tzewei_79

Hi Randy,

I understood the prefix now.


I have re-installed SMF Add-in. May I know which tab is the toolbar button for the smfFixLinks under? I can't find any special buttons on the menu bar.

Sat Oct 25, 2014 9:23 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It's a button you would have to add manually.

Which version of the add-in did you install? If you picked up the beta
version from the "Works in Progress" folder, you can find various
SMF-related items on the context menu brought up with a right mouse click
when you're in a workbook. One option there is to "Fix Links", which would
run the macro.

On Sat, Oct 25, 2014 at 8:45 PM, tzewei_79@yahoo.com.sg [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I understood the prefix now.
>
> I have re-installed SMF Add-in. May I know which tab is the toolbar button
> for the smfFixLinks under? I can't find any special buttons on the menu bar.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 21.23

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)