Hi there
Great EXCEL forums out there - for all sorts of stuff Like Mr EXCEL.com
Excel Questions.
For Financial stuff pretty neat EXCEL examples here : -- Older versions of EXCEL - but Basic Maths and Stats haven't changed all that much - unless your hunting for the Higson Particle.
Index of /Excel
I use EXCEL also for market trading - getting stuff in real time from Market sites.
One thing I like doing is Options Trading - I get data from CBOE (Chicago Board of Options Exchange in REAL TIME and insert into EXCEL). For example -- old data but you should get the idea.
If you've goty stuff that works don't upgrade to a newer version of EXCEL -- especially from 32 bit to 64 bit -- there might be a whole ton of macros that will need to be re-written.
I's stick to either 2007 or 2010 -- I tried my CBOE stuff on 2013 -- You've guessed it it failed at first go. !!
Even EXCEL 2003 could be OK but accessing the web and doing queries is a bit different so choose 2007 or 2010 --bothe versions are still currently supported by Ms and will be for at least the next 5 years or so.
Here's a sample of how to read data say form YAHOO finance in EXCEL. YAHOO can provide all sorts of stuff .
You can modify this for other data sources -- shouldn't be too hard.
Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer, iMax As Integer
Clear
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set DataSheet = ActiveSheet
For iMax = 0 To 1000 Step 200
i = 7 + iMax
If Cells(i, 1) = "" Then
GoTo stopHere
End If
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> "" And i < iMax + 207
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
' qurl = qurl + "&f=" + Range("C2")
qurl = qurl + "&f=" + "l1pjkn"
Range("c1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("Q7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("Q7:Q1200").Select
Selection.TextToColumns Destination:=Range("Q7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
' Array(6, 1), _
' Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
Range("Q7:W1200").Select
Selection.Copy
Cells(7 + iMax, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next iMax
With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With
'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
stopHere:
Range("G3") = 1
Sort
Clear2
currenttime
End Sub
Sub Clear()
'
' Clear Macro
'
'
Range("C7:H1200").Select
Selection.ClearContents
End Sub
Sub Clear2()
'
' clear2 Macro
'
Columns("Q:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
Sub Sort()
'
' Sort Macro
in_sort = "1"
'
Dim i As Integer
i = Range("G3")
If i >= 8 Then
i = 1
End If
Range("A4:G4").Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Cells(4, i).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A7:G1000").Select
Selection.Sort Key1:=Cells(7, i), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Range("K2").Select
'Selection.Copy
'Range("G2").Select
'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
' False, Transpose:=False
'Range("G2").Select
'ActiveCell.FormulaR1C1 = "=Today()"
in_sort = "0"
' Range("L3").Select
End Sub
Cheers
jimbo