Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.

[Exhelp required]

'STEP 1: Download the data from Yahoo Finance

Sub DownloadStockData()

'Declare stock ticker as string
Dim strTicker As String

'Declare input from user as string
Dim strInput As String

'Prompt user for stock ticker
strInput = InputBox("Enter Stock Ticker")

'Store stock ticker
strTicker = strInput

'Declare worksheet name
Dim wsName As String

'Define name of worksheet
wsName = strTicker

'Create worksheet and name with stock ticker
Worksheets.Add().Name = wsName

'Declare url of data
Dim URL As String

'Define url of data
URL = "http://real-chart.finance.yahoo.com...2&e=27&f=2017&g=d&a=2&b=27&c=2016&ignore=.csv" 'Declare variable to hold data Dim myData As Variant 'Retrieve data myData= GetYahooStockData(url) 'Define Chart Type Dim MyChart As Chart 'Create Chart Set MyChart = Charts.Add 'Set Chart Properties With MyChart .ChartType = xlLine .SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & UBound(myData)) End With 'Forecast Prices Dim fcst As Forecast 'create forecast Set fcst = MyChart.SeriesCollection(1).Trendlines.Add 'Set forecast parameters With fcst .Name = "Forecast" .Type = xlLinear .Forward = 2 .Backward = 2 .Intercept = 0 'Calculate and display forecast data .DataLabel.Text = "Confidence Level " & Format(.Forecast(2), "0%") 'Plot forecast result on chart MyChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = True End With

'Step 1: Write a VBA code to pull data from Yahoo finance and store it
Dim MyChart As ChartObject
Dim fcst As Trendline

'Step 2: Plot the data on a graph. Create a chart MyChart.SeriesCollection(1) from the data and plot it.

'Step 3: Add a trendline to the graph with the formula MyChart.SeriesCollection(1).Trendlines.Add

'Step 4: Set forecast parameters for the trendline by declaring the name, type (e.g. xlLinear), forward and backward periods, and confidence level

'Step 1: Define parameters
Dim ln As Name
Dim sType As XlTrendlineType
Dim sFwd As Long
Dim bFwd As Long
Dim cLevel As Double
sType = xlLinear
sFwd = 0
bFwd = 3
cLevel = 0.95

' Step 2: Create the trendline
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
Set ln = ActiveChart.SeriesCollection(1).Trendlines.Add(sType, sFwd, bFwd, cLevel)

' Step 3: Style the trendline
With ln
.Name = "Trendline"
.Border.Weight = 1.5
End With


Dim tickerSymbol As String
tickerSymbol = "AAPL"
Dim URLticker As String
URLticker = "http://ichart.finance.Yahoo.com/table.csv?s=" & tickerSymbol & "&a=00&b=01&c=1900&d=09&e=30&f=2050&g=d&ignore=.csv"

Dim stockData As String
stockData = XmlHttp.Request.Open("GET", URLticker, False)
' Step 7: Create an Excel Workbook
Dim wb As Workbook
Set wb = Workbook.Create(stockData)

' Step 8: Create a pivot table to forecast stock prices
With ActiveSheet.pivotTable
.AddDataField Range("O2:O103"), "Price Forecast", xlCustomer
.AddDataField Range("M2:M103"), "Confidence Level", xlCustomer
.RowRange = Range("M1:M2")
.AddDataField Range("N2:N103"), "Average", xlAverage
.GroupByRange Range("L2")
.ColumnGrand = False
End With

' Step 9: Create a graph using the pivot table
ActiveSheet.PivotTables("PivotTable1").TableRange2.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source = Range("Data!$M$1:$O$3")
With ActiveChart
.ChartType = xlLineMarkers
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.SeriesCollection(1).AxisGroup = 1
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stock Price"
.SeriesCollection(1).XValues = Range("Data!$N$1:$N$3")
.SeriesCollection(1).Values = Range("Data!$O$1:$O$3")
.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
End With
ActiveChart.SetElement (msoElementLegendBottom)
ActiveSheet.Shapes("Chart 1").IncrementLeft 274.67
ActiveSheet.Shapes("Chart 1").IncrementTop -193.78

'This code will forecast a stock price with a graph and confidence level.
'Step 1: Open VBA editior in Excel (Alt+F11).
'Step 2: Add the following code below.
Dim ws As Worksheet
Set ws = ActiveSheet
'Declare your variables
Dim DataRange As Range
Dim startDate As Date
Dim endDate As Date
Dim stockPrice As Range

'Get Yahoo Data
Application.Run "'GetYahooData.XLSB'!GetData"

'Set the dates (format should be dd/mm/yyyy
startDate = #1/1/2012#
endDate = #12/30/2018#

'Set the data range
Set DataRange = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).row)

'Set the stock price
Set stockPrice = ws.Range("B1:B" & ws.Cells(Rows.Count, "B").End(xlUp).row)
'Create a forecast
Dim forecast As Object
Set forecast = CreateObject("wscript.shell")
forecast.Run "Rscript --vanilla " & ThisWorkbook.Path & "\solution.rscript " & DataRange & stockPrice
'Get forecast results
Set forecastresult = ws.Range("C1:C" & ws.Cells(Rows.Count, "C").End(xlUp).row)
'Create a graph
With ws.Shapes.AddChart(xlLine, 0, 0, 300, 300)
.Chart.SetSourceData Source = forecastresult.Chart.HasTitle = True
.Chart.ChartTitle.Text = "Forecast Stock Price"
End With
'Set confidence level
ws.Range("D1") = "Confidence Level"
ws.Range("D2") = 0.95
'Format alert cell
ws.Range("A4").Interior.Color = RGB(255, 204, 0)
ws.Range("A4").Font.Bold = True
ws.Range("A4").Font.Color = RGB(0, 0, 0)
'Create a FORECAST in excel
ws.Range("I2") = "=FORECAST(E2,B2:B60,A2:A60)"
'Increase array size Ofchartarray
ReDim ChartArray(1 To 2, 1 To 160)
'Length of data array
ldata = 165
'Loop through the data array to pull stock data
For I = 2 To ldata
'Pull data from Yahoo Finance
ChartArray(1, I - 1) = ws.Range("A" & I)
ChartArray(2, I - 1) = ws.Range("B" & I)

Next I

'Create Chart and assign data points
Set cht = ws.Shapes.AddChart
With cht
.ChartType = xlLine
.SetSourceData Source:=ws.Range("A1:B" & UBound(ChartArray, 2) + 1)
.HasTitle = True
.ChartTitle.Text = "Stock Closing Prices"
End With

' Create Trendline and apply prediction
With cht.SeriesCollection(1).Trendlines(1)
.DisplayEquation = True
.Name = "Prediction"
.Type = xlLinear
.Line.ForeColor.RGB = RGB(0, 0, 255)

'Set Forecast Period
.Forward = 1
.Backward = 0
End With

' Plot confidence interval
With cht.SeriesCollection(1).Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 240)
.BackColor.RGB = RGB(255, 255, 255)
.Transparency = 0
End With
'Change chart name
ActiveChart.Name = "StockPrice Analysis"
'Construct legend
With ActiveChart.Legend.Visible = msoTrue
'Set position of legend
With .Position.Left = 1175.85
.Top = 60.75
.Width = 368.76
.Height = 150
End With
'Set legend font
With .Font
.Name = "Calibri"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(211, 211, 211)
.Size = 14
End With
'Select legend Fill
With ActiveChart.Legend.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(211, 211, 211)
End With
End With
'Set Overall Chart Report Title
With ActiveChart
.ChartTitle.Text = Range("A1").Value & ": Forecast Price with Confidence Interval"
'Set X and Y axes
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Time" '.AxisTitle.Font.Size = 12
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Stock Price"
'.AxisTitle.Font.Size = 12
End With
'Setting the chart is completed!
End With

'Retrieve data from Yahoo Finance

Dim yfURL As String
Dim FinalRow As Long
Dim qt As QueryTable

FinalRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row 'Number of rows

yfURL = "http://real-chart.finance.yahoo.com/table.csv?s=" + stockName
'url from finance.yahoo.com
Set qt = Sheet1.QueryTables.Add(Connection:=yfURL, Destination:=Sheet1.Range("A2"))
' adds query table to excel sheet
qt.BackgroundQuery = False
qt.Refresh qt.SaveData = True
'Save data in worksheet
FinalRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row
'Used to find last row with data setting graph chart
'Set Chart Chart type is a line graph

Sheet1.Range("A2:B" & FinalRow).Select
ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlLine

'Label Axes

With ActiveChart.Axes(xlCategory, a)
.HasTitle = True
.AxisTitle.Characters.Text = "Date"
End With
'Label Horizontal Axis
With ActiveChart.Axes(xlValue, xlPrimary)
With .AxisTitle.Characters.Text = "Price"
End With
ActiveChart.SetSourceData Source:=rs

'Confidence Level
.Sheet1 Range("A2:B" & FinalRow)
.Select ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineActiveChart.HasLegend = False
'Add confidence band
With ActiveChart.SeriesCollection.NewSeries.Values = Sheet1.Range("P2:P" & FinalRow)
.Axes(xlValue).CrossesAt = 0
.SeriesCollection(1).ChartType = xlLine
.SeriesCollection(1).Border.Weight = xlMedium
.SeriesCollection(1).Border.ColorIndex = 8
.SeriesCollection(2).Name = "Confidence Level"
.SeriesCollection(2).ChartType = xlLine
.SeriesCollection(2).Border.Weight = xlMedium
.SeriesCollection(2).Border.ColorIndex = 30

'Step 1: Open the Microsoft Excel program and create a new worksheet for storing our stock market data.

'Step 2: Open the VBA Editor window (hit Alt + F11).

'Step 3: Expand the Project Explorer window and navigate to your worksheet.

'Step 4: Select the worksheet, then click Insert >Module.

'Step 5: Insert this VBA code in the Module1 window:

Sub ForecastStockPrice()

End Sub
'This code will forecast the stock price of a company
'First, we will obtain the necessary data
Dim startDate As Date
Dim endDate As Date
startDate = InputBox("Please enter the start date:")
endDate = InputBox("Please enter the end date:") 'Set up Excel objects Dim ws As Excel.Worksheet Dim series As Series Dim chrt As Chart Set ws = ActiveSheet Set chrt = ws.ChartObjects.Add(0, 0, 1000, 600).Chart chrt.ChartType = xlLine 'Download the adjusted close prices With ws.QueryTables.Add(Connection:= _ "URL;Symbol Lookup from Yahoo Finance "+ tickerName + _ "&a=" + Month(startDate) + "&b=" + Day(startDate) + _ "&c=" + Year(startDate) + "+&d=" + Month(endDate) + _ "&e=" + Day(endDate) + "&f=" + Year(endDate) + "&g=d", _ Destination:= ws.Range("A2"))

Step 1

'Create a new worksheet and label it "Forecast".

Step 2

'Create a new VBA Script and type the following code:

Sub StocksForecast()

Dim endDate As Date, startDate As Date, x As Integer

endDate = Date ' Define what date is your current date
startDate = DateAdd("m", -x, endDate) 'Define the start date by subtracting x number of months from end date

'Download the data from Yahoo Finance
With ActiveSheet.QueryTables.Add(Connection:= "URL;https://finance.yahoo.com/quote/AAPL/history?p=AAPL &a=" & Month(startDate) & "&b=" & Day(startDate) & "&c=" _
& Year(startDate) + "&d=" & Month(endDate) & "&e=" & Day(endDate) & "&f=" & Year(endDate) & _
"&g=d"

'Step 1: Begin by opening a new excel workbook and saving the file.

'Step 2: Go to the VBE (Visual Basic Editor) by pressing Alt + F11 and double click on the sheet where the code is to be written.

'Step 3: Once inside the VBE, go to Insert and select Module.

'Step 4: Now declare the variables to store the data required by the code. For example:

Dim URL As String 'to store the URL of the Yahoofinance website
Dim startDate As Date 'to store the start date of the stock data
Dim endDate As Date 'to store the end date of the stock data

'Step 5: Now, initialize the variables to the appropriate values. For example:



Dim stockPrice As Double
Dim stockDataYear As Integer
Dim stockDataMonth As Integer
Dim forecastValue As Double
Dim confidenceLevel As Double

'Step 6: Manual inputs to these variables will be provided by the user to form the graph and compute the forecasting values.

'For example:

stockPrice = 100 '--Enter the stock price here
stockDataYear = 2018 '--Enter the year here
stockDataMonth = 7 '--Enter the month here
confidenceLevel = 95 '--Enter the forecasting confidence level here

'Step 7: Set up a For loop to capture the historical stock data from Yahoo Finance.

For I = 1 To 12
stockDataUrl = "Symbol Lookup from Yahoo Finance" & stockName & "/history?period1=" & stockDataMonth & "/1/" & stockDataYear & "&period2=" & stockDataMonth & "/31/" & stockDataYear & "&interval=1d&events=history&crumb="


'1. Define a variable for the stock data month (For example: stockDataMonth).

'2. Define a variable for the stock data year (For example: stockDataYear).

'3. Set up a For Loop to go through every period from the stockDataMonth and stockDataYear.

'4. Within the For Loop, use a Yahoo Finance API to collect the daily closing date for the stock data.

'5. Create a graph using the automated method "ChartObject", to visualize the data and to see the trend.

'6. Find the confidence level from the stock data, using the "T.Test" Statistical function.

'7. Insert a line of best-fit to the graph, to use as the forecast.

'8. Print the forecast to the worksheet.

'9. End the VBA code.

Sub ForecastStock_Price()

End Sub

' step 1: declare variables
Dim stockName As String
Dim numDays As Integer
Dim price As Range
Dim forecast As Range

' step 2: define variables
stockName = "TickerSymbol"
numDays = 30
Set price = Range("A1:B6")
Set forecast = Range("C1:D6")

' step 3: request and apply data from Yahoo Finance
With ActiveSheet.QueryTables.Add(Connection:="URL;Symbol Lookup from Yahoo Finance" & stockName & "&a=00&b=1&c=1948&d=04&e=27&f=2016&g=d", Destination:=price)
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Step 1:
'Write a VBA Code that will allow you to forecast the stock price with a graph and confidence level of the chosen stock.
'Step 2:
'In the VBA Code, you need to define your stock name by stating "stockName".
'Step 3:
'You also need to define the complete URL of the chosen stock. The URL should be set for "Symbol Lookup from Yahoo Finance" & stockName & "&a=00&b=1&c=1948&d=04&e=27&f=2016&g=d" which will allow you to access the necessary data from Yahoo Finance.
'Step 4:
'Within the VBA Code, add an ActiveSheet.QueryTables.Add then set its connection string to the URL given in Step 3. You will also need to set the Destination for this object.

'Instructions:

'Step 1: Open Microsoft Visual Basic for Applications (VBA)

'Step 2: Create a new macro in the VBA editor by navigating to the "Insert" tab and selecting "Module"

'Step 3: Download historical stock data from Yahoo Finance into an excel workbook.

'Step 4: Acquire the data by setting a range of the values in the worksheet.

'Step 5: Enter the following VBA code in the macro window:

Sub Forecast_Stock_Price()

End Sub

Dim dataStartRow As Integer
dataStartRow = 2

ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=Range(Cells(dataStartRow, 1), Cells(dataStartRow, 2))
.Location Where:=xlLocationAsNewSheet

End With

'1. Open a blank Microsoft Excel spreadsheet.

'2. Navigate to the Developer tab and click on "Visual Basic" to open the VBA IDE.

'3. Paste the following code into the window:
Sub Forecast_StockPrice()

'Define Constants
Dim dataStartRow As Integer
dataStartRow = 2

' Turn Off Screen Updating
Application.ScreenUpdating = False

' Create a New Chart
ActiveSheet.Shapes.AddChart.Select

With ActiveChart
.ChartType = xlLine
.SetSourceData
Source = Range(Cells(dataStartRow, 1), Cells(dataStartRow + 4, 2))
.Location Where:=xlLocationAsNewSheet
End With

' Turn On Screen Updating
Application.ScreenUpdating = True
End Sub

'4. In the Excel workbook, select the cell that contains the stock symbol.

'5. In the Visual Basic Editor window, go to View --> Immediate Window

'6. In the Immediate Window, type in queryStocks("Symbol"), replacing Symbol with the stock symbol entered in the Excel workbook
'7. This will return the stock's data from Yahoo! Finance and create a Chart, displaying the forecasted price with a 90% confidence interval.

 

[HaHoBe]

Hi there,

please use code-tags to present your procedures here, see the description How to Post Your VBA Code.

There are a lot of comments showing numbers but the code doesn't follow them - any reason for that?

In a standard module you place either Procedures (Subs) or Functions who start with a line holding the keyword like Sub DownloadStockData() and end with End Sub. All code to be executed must be placed in between those, and there may be no other procedures or Functions in between these codelines.

You should make use of the Debugger in the VBE to find out some of the problems with the pasted code.

Your title states ...can't quite fix it. Maybe it would be a good idea to explain what you have done and where the problem is.

Holger

 

[Exhelp required]

Appreciate your reply. If you know what you are looking at, you can see there is more than 1 code attempting to do the same thing.

Do you log in to tell everyone they should use the debigger assuming they have not?
Your assumptions and complaining about what you think I didn't do doesn't really help anything And seems to go against the spirit of this site.

 

[profray]

Well, you did post a lot of stuff to look at. I Copy/Pasted your listing into my VBA and got about 400 lines of code. It would be difficult for anyone to step into such a problem "cold"

You have already divided your task into subtasks (a good thing) but it is difficult to know if any of these subtasks work. Starting from zero, here are my questions for you:
1. There are two levels of "having problems" with code like this. Are you having "totally not working" sort of problem, or "not giving me the right answers" problem.
2. Totally not working would mean that you are still getting syntax errors or referencing routines outside Excel VBA that are not recognized. Simple syntax errors are handled by VBA IDE where you get the beep and red text and something you wrote is not understood by VBA on a very fundamental level.
3. Totally not working may also mean that the communication you are looking for just hangs and you can't find the problem because nothing is happening that you can investigate.
4. Not giving the right answers means that you get something that kind of looks like what you want, but the data is garbled, or the graphs don't look right etc.. Many times you can go into debug mode and slowly (painfully??) figure out what is correct and what is not. But it still requires a plan, and a plan needs and objective.
So, if you need help with this routine, it would be great to describe what is and is not working.
Let me know ...