Pulling data points from multiple webpages

[Jimmywinvests]

Hello Exel-sperts (yes i love bad puns),

I have spent weeks reading forums and watching videos only to fail at creating the spreadsheet I set out to; I am hoping that someone on here might be able to help me.
I need to create a spreadsheet which pulls multiple data points (from a few web pages) off yahoo finance. The method however would need to include a macro button which when pressed would refresh the data, as well as work for multiple assessments.

The data I wish to extract (using the company "Telstra" as an example) include the following:

Summary Page

https://au.finance.yahoo.com/q?s=TLS.AX

Company Name

share price

Market Cap (mil)

Balance Sheet Quartly Page

https://au.finance.yahoo.com/q/bs?s=TLS.AX

Cash and Cash equilavents (of most recent quarter - i.e. left column)

Cash from short term investments (of most recent quarter - i.e. left column)

Cash Flow Annual Page

https://au.finance.yahoo.com/q/cf?s=TLS.AX&annual

Cash from operating activities (most recent annual figure)

Capital expenditures (most recent annual figure)

Key Statistics Page

https://au.finance.yahoo.com/q/ks?s=TLS.AX

shares outstanding

return on equity

revenue growth rate

Total debt

Operating

Forward PE Ratio (optional - include if easy)

<tbody>
</tbody>

The idea is that this data is scraped off the above websites using the ticker "TLS.AX" as the identifier. Therefore, the headers should populate the first row in the spreadsheet.

The second part to this is that I would need this to work on multiple companies at a time (for example if i listed the circa 2100 companies listed on the ASX in column "A", ideally it would populate all the above listed data for every company; thus allowing the filtering of companies by data point.

Any help is very greatly appreciated. (I am not sure where else to turn to - at this point I even have doubts that excel can fulfill the requirements).

Thank-you to anybody who takes the time to read my problem!

- Jimmy

 

[Jimmywinvests]

Looks like everything is working beautifully! - aside from it randomly stopping at different increments.

Earlier where you said to change the application to from 2 to 3 seconds... Should I do this wherever it occurs throughout the macro?

thanks again for everything. wish I could send you a gift basket

 

[michaelsmith559]

I'm not sure why it randomly stops unless its just not allowing enough time to download everything or if the webpage is taking a little bit longer to respond. That is beyond me. You could start a new post and ask for any help speeding up the macro and ask why it randomly stops. Other than that I'm really not sure. Glad you like it.
Oh, yes everywhere it says application.wait....2, change the 2 to a 3 and see if that helps.

Mike

 

[Jimmywinvests]

Thank you so very much again! More than helpful. Will start a new thread tomorrow morning on the way to work.

 

[michaelsmith559]

I also made a sheet to try and predict the probability of a stock being profitable using the historical data. You may find this useful or you may not care for it at all. The one I did, has an accuracy of 83%. I'll post several parts of how to set up the sheet followed by macros, and a function. Compare column G (actual) to column M (predicted).

Excel 2003
	A	B	C	D	E	F	G
1	Open	High	Low	Volume	Close	Profit	Profit
2	2.45	2.45	2.18	335,200	2.3	0	0
3	2.41	2.41	2.35	82,600	2.4	0	0
4	2.4	2.46	2.31	111,700	2.4	0	0
5	2.43	2.48	2.4	108,400	2.4	0	0
6	2.42	2.45	2.41	113,600	2.43	1	1
7	2.39	2.43	2.35	118,800	2.39	0	0
8	2.39	2.4	2.3	61,300	2.3	0	0
9	2.32	2.43	2.3	80,600	2.37	1	1
10	2.4	2.4	2.25	142,600	2.28	0	0
11	2.24	2.33	2.24	278,100	2.33	1	1
12	2.05	2.25	2.05	60,700	2.2	1	1
13	2.01	2.04	1.99	75,900	2.04	1	1
14	2.01	2.04	1.99	35,000	2.04	1	1
15	2.02	2.07	2	116,300	2.01	0	0
16	2.05	2.06	2	60,200	2.03	0	0
17	2.14	2.15	2.04	137,900	2.1	0	0
18	2.06	2.11	2.04	39,200	2.11	1	1
19	2.04	2.05	2.01	49,400	2.05	1	1
20	2.05	2.08	2.03	45,500	2.06	1	1
21	2.06	2.08	2.03	65,100	2.08	1	1
22	2.07	2.07	2.02	491,100	2.05	0	0
23	2.08	2.09	2.02	62,100	2.07	0	0
24	2.08	2.08	2.02	14,000	2.08	0	0
25	2.08	2.08	2.02	35,400	2.08	0	0
26	2.08	2.08	2.08	0	2.08	0	0
27	2.08	2.11	2.05	119,600	2.08	0	0
28	2.01	2.07	1.98	143,100	2.05	1	1
29	2.06	2.08	1.95	40,900	1.96	0	0
30	1.98	2.03	1.98	83,300	2	1	1
31	1.96	1.98	1.96	15,100	1.98	1	1
32	1.98	1.98	1.93	55,400	1.93	0	0
33	2	2.15	2	157,300	2.02	1	1
34	1.98	2	1.98	65,500	2	1	1
35	1.98	1.98	1.97	33,900	1.98	0	0
36	1.96	1.98	1.94	22,100	1.98	1	1
37	1.94	1.96	1.9	128,300	1.96	1	1
38	1.91	1.97	1.89	90,100	1.95	1	1
39	1.96	1.98	1.95	65,100	1.96	0	0
40	1.99	1.99	1.95	43,600	1.96	0	0
41	1.97	1.98	1.95	88,800	1.96	0	0
42	1.93	1.97	1.93	114,200	1.97	1	1
43	1.93	1.93	1.93	0	1.93	0	0
44	1.93	1.93	1.88	31,800	1.93	0	0
45	1.92	1.93	1.9	45,900	1.93	1	1
46	1.9	1.96	1.87	95,500	1.92	1	1
47	1.9	1.9	1.9	0	1.9	0	0
48	1.9	1.9	1.9	0	1.9	0	0
49	1.9	1.9	1.86	45,700	1.9	0	0
50	1.89	1.91	1.88	86,900	1.9	1	1
51	1.85	1.88	1.85	66,800	1.88	1	1
52	1.85	1.85	1.85	88,600	1.85	0	0
53	1.85	1.88	1.85	72,200	1.85	0	0
54	1.89	1.89	1.83	159,100	1.85	0	0
55	1.84	1.88	1.84	66,800	1.84	0	0
56	1.83	1.86	1.8	108,800	1.85	1	1
57	1.84	1.84	1.79	225,500	1.83	0	0
58	1.8	1.84	1.8	78,100	1.82	1	1
59	1.78	1.85	1.78	351,100	1.8	1	1
60	1.71	1.94	1.71	214,600	1.76	1	1
61	1.62	1.65	1.61	138,000	1.64	1	1
62	1.59	1.63	1.59	193,000	1.61	1	1
63	1.62	1.63	1.59	210,500	1.6	0	0
64	1.65	1.65	1.59	330,100	1.63	0	0
65	1.68	1.68	1.59	260,800	1.65	0	0
66	1.67	1.69	1.63	161,400	1.69	1	1
67	1.68	1.7	1.65	65,200	1.65	0	0
Sheet1

Excel 2003
	I	J	K	L	M
1	Logit	Exp(Logit)	P(X)	P(Y)	Predict Profit
2	-11.7329	8.02521E-06	8.02515E-06	0.999992	0
3	-4.28724	0.013742745	0.013556442	0.986444	0
4	-0.80379	0.447631231	0.30921634	0.690784	0
5	0.217009	1.242355706	0.554040424	0.44596	1
6	-0.64935	0.52238414	0.343135564	0.343136	0
7	-0.75493	0.470044719	0.319748585	0.680251	0
8	-4.33034	0.01316302	0.012992006	0.987008	0
9	5.420524	225.9974366	0.995594664	0.995595	1
10	-7.23125	0.000723616	0.000723093	0.999277	0
11	4.093263	59.93512371	0.983589104	0.983589	1
12	13.61232	816120.2777	0.999998775	0.999999	1
13	0.378471	1.460050334	0.593504252	0.593504	1
14	0.520235	1.682422343	0.627202628	0.627203	1
15	1.701315	5.481148333	0.845706355	0.154294	1
16	-2.09867	0.12261927	0.109226051	0.890774	0
17	-4.16871	0.015472145	0.015236405	0.984764	0
18	1.851047	6.366482091	0.864249992	0.86425	1
19	-1.41732	0.242361626	0.195081384	0.195081	0
20	0.366336	1.442440124	0.590573382	0.590573	1
21	-0.78437	0.456407546	0.313379004	0.313379	0
22	-4.39709	0.012313078	0.01216331	0.987837	0
23	-2.50065	0.082031391	0.075812395	0.924188	0
24	-3.08006	0.045956553	0.043937344	0.956063	0
25	-3.15423	0.042671096	0.04092479	0.959075	0
26	-1.18792	0.304853733	0.233630579	0.766369	0
27	-0.2859	0.751338646	0.429008203	0.570992	0
28	2.076656	7.977746223	0.88861347	0.888613	1
29	-3.15864	0.042483637	0.04075233	0.959248	0
30	2.547737	12.77815594	0.927421347	0.927421	1
31	0.604502	1.830340489	0.646685618	0.646686	1
32	-2.62253	0.072619161	0.067702651	0.932297	0
33	9.693745	16215.86677	0.999938336	0.999938	1
34	0.371058	1.449267328	0.591714637	0.591715	1
35	-1.31893	0.267420822	0.210996077	0.789004	0
36	-0.0343	0.966284034	0.491426476	0.491426	1
37	-0.95818	0.383589452	0.277242249	0.277242	0
38	2.861389	17.48579206	0.945904401	0.945904	1
39	0.123928	1.131934788	0.530942501	0.469057	1
40	-2.30373	0.099885161	0.090814173	0.909186	0
41	-1.04099	0.353105641	0.260959404	0.739041	0
42	1.84139	6.305294278	0.863112975	0.863113	1
43	-0.74728	0.47365218	0.321413822	0.678586	0
44	-2.39385	0.091277953	0.083643175	0.916357	0
45	-0.74541	0.474538898	0.321821892	0.321822	0
46	2.564779	12.99779091	0.928560156	0.92856	1
47	-0.65915	0.517288883	0.340929726	0.65907	0
48	-0.65915	0.517288883	0.340929726	0.65907	0
49	-2.04663	0.129169696	0.11439352	0.885606	0
50	0.254	1.289171559	0.563160744	0.563161	1
51	1.494566	4.45740354	0.81676268	0.816763	1
52	-0.81937	0.440709057	0.305897332	0.694103	0
53	1.475849	4.374750374	0.813944848	0.186055	1
54	-3.02485	0.048565335	0.046315983	0.953684	0
55	2.270068	9.680056483	0.906367536	0.093632	1
56	0.485937	1.625697676	0.619148842	0.619149	1
57	-2.80085	0.060758643	0.05727848	0.942722	0
58	2.348405	10.46885912	0.912807369	0.912807	1
59	3.699286	40.41843893	0.975856164	0.975856	1
60	16.31605	12189014.95	0.999999918	1	1
61	1.616161	5.033729329	0.834265022	0.834265	1
62	2.567047	13.02729989	0.928710443	0.92871	1
63	-0.74192	0.476199351	0.322584718	0.677415	0
64	-2.91252	0.054338435	0.051537944	0.948462	0
65	-3.68226	0.025166144	0.024548356	0.975452	0
66	-0.27976	0.755967837	0.430513487	0.430513	1
67	0.331575	1.393160893	0.582142595	0.417857	1
Sheet1

Cell Formulas
Range	Formula
I2	=$O$1+$O$2*A2+$O$3*B2+$O$4*C2+$O$5*D2
J2	=EXP(I2)
K2	=J2/(1+J2)
L2	=IF(G2=1,K2,1-K2)

Excel 2003
	N	O	P	Q	R
1	Constant	4.922300843
2	A	-108.2769669
3	B	74.61252287
4	C	30.72683636
5	D	-3.46611E-06
6
7	Product	5.2995E-12
8	LL	-25.96340904
9			In Range	Min	Max
10	Open	1.68	Yes	1.59	2.45
11	High	1.7	Yes	1.63	2.48
12	Low	1.65	Yes	1.59	2.41
13	Volume	65200	Yes	0	491100
14	Logit	0.331575189
15	e^Logit	1.393160893
16
17	Probability	58.21%
18	Classify	1
19
Sheet1

Cell Formulas
Range	Formula
O1	=T2
O2	=U2
O3	=V2
O4	=W2
O5	=X2
O7	=PRODUCT(L:L)
O8	=LN(O7)
O14	=O1+O2*O10+O3*O11+O4*O12+O5*O13
O15	=EXP(O14)
O17	=O15/(1+O15)
O18	=IF(O17>=T26,1,0)
P10	=IF(AND(O10>=Q10, O10<=R10),"Yes","No")
P11	=IF(AND(O11>=Q11, O11<=R11),"Yes","No")
P12	=IF(AND(O12>=Q12, O12<=R12),"Yes","No")
P13	=IF(AND(O13>=Q13, O13<=R13),"Yes","No")
Q10	=MIN(A:A)
Q11	=MIN(B:B)
Q12	=MIN(C:C)
Q13	=MIN(D:D)
R10	=MAX(A:A)
R11	=MAX(B:B)
R12	=MAX(C:C)
R13	=MAX(D:D)

Excel 2003
	S	T	U	V	W	X
1		Beta0	Beta1	Beta2	Beta3	Beta4
2	Coeff	4.922300843	-108.277	74.61252	30.72684	-3.46611E-06
3	SE(Beta)	4.099938642	28.34705	23.5439	18.50453	7.13414E-06
4	z-stat	1.200579148	-3.819691	3.169081	1.660504	-0.485847492
5	p-value	22.99%	0.01%	0.15%	9.68%	62.71%
6	McFaddenR2	42.91%
7	Cox&SnellR2	44.64%
8	Iterations	6
9	LR	39.02240141
10	LR p-value	6.89262E-08
11	xxxxx	xxxxx	xxxxx	xxxxx	xxxxx	xxxxx
12		ActualResponse
13	Prediction	Positive	Negative
14	Positive	25	6
15	Negative	5	30
16	xxxxx	xxxxx	xxxxx	xxxxx	xxxxx	xxxxx
17	Accuracy	83.33%
18	ErrorRate	16.67%
19	HitRate	83.33%
20	TrueNegRate	83.33%
21	FalsePos	16.67%
22	Precision	80.65%
23	NegPredVal	85.71%
24	FalseDiscover	19.35%
25	xxxxx	xxxxx	xxxxx	xxxxx	xxxxx	xxxxx
26	CutOff	43.00%
27	Accuracy	83.33%
Sheet1

Cell Formulas
Range	Formula
T26	=INDEX(S29:S119,MATCH(MAX(T29:T119),T29:T119,0))
T27	=(T14+U15)/(T14+T15+U14+U15)
S1:X25	{=logit(G2:G67,A2:D67,T26,TRUE,TRUE)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Sub Maximize_CutOff_Value_Logistic_Regression_Example()
Dim i As Single, RowNumber As Integer
RowNumber = 29
Application.ScreenUpdating = False
With ActiveSheet
    For i = 0.1 To 1 Step 0.01
    .Cells(26, 20) = i
    .Cells(RowNumber, 19) = i
    .Cells(RowNumber, 20) = .Cells(27, 20)
    RowNumber = RowNumber + 1
Next i
.Cells(26, 20).Value = "=Index(S29:S119,Match(Max(T29:T119),T29:T119,0))"
End With
Application.ScreenUpdating = True
End Sub

Sub Predict_Y()
With ActiveSheet
Application.ScreenUpdating = False
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For R = 2 To lastRow
    Range("O10").Value = Range("A" & R).Value
    Range("O11").Value = Range("B" & R).Value
    Range("O12").Value = Range("C" & R).Value
    Range("O13").Value = Range("D" & R).Value
    Range("M" & R).Value = Range("O18").Value
Next R
End With
Application.ScreenUpdating = True
End Sub

Function:

Option Explicit
Option Base 1

Function Logit(known_y As Range, known_x As Range, Cutoff As Double, Optional Constant As Boolean = True, Optional Stats = False)
    Dim Intercept As Double
    If Constant = True Then
     Intercept = 1
    ElseIf Constant = False Then
     Intercept = 0
    End If

Dim M As Integer:  M = known_x.Columns.Count + Intercept ' number of independent variables
Dim N As Integer:  N = known_x.Rows.Count ' number of observations
Dim IndVar() As Double:  ReDim IndVar(1 To N, 1 To M) As Double
Dim ii As Integer:  ii = 1
Dim jj As Integer:  jj = 1
Dim kk As Integer:  kk = 1
Dim y_bar As Double:  y_bar = 0

For ii = 1 To N
    y_bar = y_bar + known_y(ii) ' calculate the average y
    If Intercept = 1 Then
     IndVar(ii, 1) = 1 ' create intercept
    End If
    For jj = 1 + Intercept To M
     IndVar(ii, jj) = known_x(ii, jj - Intercept) ' load in independent variables
    Next jj
    Next ii
    y_bar = y_bar / N ' average y for calculation of r2

Dim MaxIt As Integer:   MaxIt = 100 ' max number of itertions in Newton Algorithim
Dim cc As Integer:  cc = 1 ' main loop counter
Dim epsilon As Double:  epsilon = 0.000001 ' convergence criteriaq of Newton Algorithim
Dim Err As Double:  Err = 1 ' measure of convergence of Newton Algorithim
Dim y_hats() As Double: ReDim y_hats(1 To N) As Double ' model forecast
Dim Betas() As Double:  ReDim Betas(1 To M) As Double ' estimated betas
Dim z() As Double:  ReDim z(1 To N) As Double
Dim J() As Double:  ReDim J(1 To M) As Double ' Jacobian
Dim H() As Double:  ReDim H(1 To M, 1 To M) As Double ' Hessian
Dim Newt() As Variant:  ReDim Newt(1 To M) As Variant ' Newton Gain
Dim LogLikelihood As Double:    LogLikelihood = 1
Dim LogLikelihoodP As Double:   LogLikelihoodP = 1

Do While cc < MaxIt
 For ii = 1 To N
  For jj = 1 To M
    z(ii) = z(ii) + Betas(jj) * IndVar(ii, jj)
Next jj
y_hats(ii) = 1 / (1 + Exp(-1 * z(ii))) ' model estimate
For jj = 1 To M
 J(jj) = J(jj) + (known_y(ii) - y_hats(ii)) * IndVar(ii, jj)  ' Jacobian
  For kk = 1 To M
   H(jj, kk) = H(jj, kk) - y_hats(ii) * (1 - y_hats(ii)) * IndVar(ii, jj) * IndVar(ii, kk) ' Hessian
Next kk
Next jj
LogLikelihood = LogLikelihood + (known_y(ii) * Log(y_hats(ii)) + (1 - known_y(ii)) * Log(1 - y_hats(ii)))
Next ii

If Abs(LogLikelihood - LogLikelihoodP) < epsilon Then Exit Do ' check if converged, exit if true
LogLikelihoodP = LogLikelihood

Newt = Application.WorksheetFunction.MMult(J, Application.WorksheetFunction.MInverse(H))
For jj = 1 To M
 Betas(jj) = Betas(jj) - Newt(jj)
Next jj

ReDim J(1 To M): ReDim H(1 To M, 1 To M): ReDim z(1 To N) As Double ' clear Jacobian and Hessian matrix
LogLikelihood = 0
cc = cc + 1
Loop

If Stats = False Then ' if stats not selected the output betas and labels
  ReDim Output(1 To 2, 1 To M) As Variant
    For ii = 1 To M
      Output(1, ii) = "Beta" & (ii - 1)
      Output(2, ii) = Betas(ii)
    Next ii
  Logit = Output
  Exit Function
End If

Dim HInv() As Variant: ReDim HInv(1 To M, 1 To M) As Variant
Dim Tstat() As Double: ReDim Tstat(1 To M) As Double
HInv = Application.WorksheetFunction.MInverse(H)
ReDim Output(1 To 25, 1 To M + 1) As Variant
For ii = 1 To M
  Output(1, ii + 1) = "Beta" & (ii - 1) ' label
  Output(2, ii + 1) = Betas(ii) ' betas
  Output(3, ii + 1) = Sqr(-HInv(ii, ii)) ' standard errors
  Output(4, ii + 1) = Output(2, ii + 1) / Output(3, ii + 1)
  Output(5, ii + 1) = (1 - Application.WorksheetFunction.NormSDist(Abs(Output(4, ii + 1)))) * 2 ' p-value
Next ii
  Output(1, 1) = ""
  Output(2, 1) = "Coeff"
  Output(3, 1) = "SE(Beta)"
  Output(4, 1) = "z-stat"
  Output(5, 1) = "p-value"

Dim LogLikelihood0 As Double: LogLikelihood0 = N * (y_bar * Log(y_bar) + (1 - y_bar) * Log(1 - y_bar))
Output(6, 1) = "McFaddenR2":    Output(6, 2) = 1 - LogLikelihood / LogLikelihood0
Output(7, 1) = "Cox&SnellR2":   Output(7, 2) = 1 - Exp(-2 / N * (LogLikelihood - LogLikelihood0))
Output(8, 1) = "Iterations":    Output(8, 2) = cc - 1
Output(9, 1) = "LR":        Output(9, 2) = 2 * (LogLikelihood - LogLikelihood0)
Output(10, 1) = "LR p-value":   Output(10, 2) = Application.WorksheetFunction.ChiDist(Output(9, 2), M - 1) ' p-value for LR

' Contingency table
Dim N_TP As Integer:  Dim N_TN As Integer
Dim N_FP As Integer:  Dim N_FN As Integer

For ii = 1 To N
  If known_y(ii) = 1 And (y_hats(ii) - Cutoff) > 0 Then
    N_TP = N_TP + 1
  ElseIf known_y(ii) = 0 And (y_hats(ii) - Cutoff) <= 0 Then
    N_TN = N_TN + 1
  ElseIf known_y(ii) = 0 And (y_hats(ii) - Cutoff) > 0 Then
    N_FP = N_FP + 1
  ElseIf known_y(ii) = 1 And (y_hats(ii) - Cutoff) <= 0 Then
    N_FN = N_FN + 1
  End If
Next ii

Output(12, 1) = "": Output(12, 2) = "ActualResponse":   Output(13, 1) = "Prediction"
Output(13, 2) = "Positive": Output(13, 3) = "Negative"
Output(14, 1) = "Positive": Output(15, 1) = "Negative"
Output(14, 2) = N_TP:       Output(14, 3) = N_FP
Output(15, 2) = N_FN:       Output(15, 3) = N_TN

Output(17, 1) = "Accuracy": Output(17, 2) = (N_TP + N_TN) / (N_TP + N_TN + N_FP + N_FN)
Output(18, 1) = "ErrorRate":    Output(18, 2) = 1 - Output(17, 2)
Output(19, 1) = "HitRate":  Output(19, 2) = N_TP / (N_TP + N_FN)
Output(20, 1) = "TrueNegRate":  Output(20, 2) = N_TN / (N_TN + N_FP)
Output(21, 1) = "FalsePos": Output(21, 2) = 1 - Output(20, 2)

Output(22, 1) = "Precision":        If N_TP + N_FP = 0 Then Output(22, 2) = "Error" Else Output(22, 2) = N_TP / (N_TP + N_FP)
Output(23, 1) = "NegPredVal":       If N_TN + N_FP = 0 Then Output(23, 2) = "Error" Else Output(23, 2) = N_TN / (N_TN + N_FN)
Output(24, 1) = "FalseDiscover":    If N_FP + N_TP = 0 Then Output(24, 2) = "Error" Else Output(24, 2) = N_FP / (N_FP + N_TP)

For ii = 1 To M + 1
  Output(11, ii) = "xxxxx": Output(16, ii) = "xxxxx":   Output(25, ii) = "xxxxx"
Next ii
For ii = 3 To M + 1
    Output(6, ii) = "": Output(7, ii) = "": Output(8, ii) = ""
    Output(9, ii) = "": Output(10, ii) = "":    Output(12, ii) = ""
    Output(17, ii) = "":    Output(18, ii) = "":    Output(19, ii) = ""
    Output(20, ii) = "":    Output(21, ii) = "":    Output(22, ii) = ""
    Output(23, ii) = "":    Output(24, ii) = "":
Next ii
For ii = 4 To M + 1
    Output(13, ii) = "":    Output(14, ii) = "":    Output(15, ii) = ""
Next ii
Logit = Output

End Function

Hope you like.

Mike

 

[michaelsmith559]

I was not sure if you wanted to add a "probability of profit" column to the final summary sheet. Also, do not put too much "stock", no pun intended, into the above logistic regression. Let me know what you think.

 

[michaelsmith559]

Sorry for the above post #34 & 35 being posted here. It was meant to be a private message.

 

[Jimmywinvests]

That looks very interesting! I cannot wait to run it after work today.

 

[Jimmywinvests]

I'm thinking this might work well as a second spreadsheet. Additionally I think I will have a go at copy pasting the current price sections of the macro into its own spreadsheet (as this value changes much more frequently and it seems near impossible to run the whole macro everyday!

 

[michaelsmith559]

Hope you like it. Glad to help.

Mike