Search
× Search
Friday, April 19, 2024

Archived Discussions

Recent member discussions

The Algorithmic Traders' Association prides itself on providing a forum for the publication and dissemination of its members' white papers, research, reflections, works in progress, and other contributions. Please Note that archive searches and some of our members' publications are reserved for members only, so please log in or sign up to gain the most from our members' contributions.

Help to use stocks Excel datas

photo

 Josias K., Financial Markets Analyst

 Monday, August 18, 2014

Hello! I would like to make analysis on a financial market underdeveloped in terms of tools. What I want to do is get the daily summaries of the stocks activities and analyze them to build strategies. the problem is that the daily summaries are in PDF files. I found a way to convert pdf files into Excel files , to retrieve data such as above, below, volumes, etc. that will forge the base of the analyzes. These datas are in columns. The minimum I would like to do is to analyze the graphs of shares in this market, so the software should be able to load an excel file containing stock data and exploit them. I already thought about doing it on excel directly using macros that will sort the data, and create buttons that will do the job, but it must already be another way that will take less time and be more effective, such as an existing software. Do you know a free or paid software, preferably free, that can use these data to build candlestick charts for technical analysis, indicators, etc? Thanks


Print

5 comments on article "Help to use stocks Excel datas"

photo

 Daniel J., Stock Market Analyst and Trader, Automated Trading System Developer

 Tuesday, August 19, 2014



I would use Apache PDFBox Library (open-source) to write a program that extract data from pdf files and write back the data into a csv or ascii file then import that file from MotiveWave (commercial). Once the data loaded you can develop your strategies and studies using its SDK (Java Programming Language).


photo

 private private,

 Tuesday, August 19, 2014



Hello,



Copy below and paste into an Excel Module. Put the symbol for the company you want into Cell A1, then run the macro. It will get you the data for US stocks. Excel does not do very good candlestick graphs or at least it did not through V2007. Maybe it can now. Hope this will help.



Thanks, David



Sub YahooQuery()


'3/17/2014 by David Lanman


Dim QT As QueryTable


Range("A1").Select


Range("A2:K10000").Clear


Dim Msg, Style, Title, Response, MyString


Msg = "Yahoo Price History. Start from cell with Symbol in it, continue ?"


Style = vbOKCancel


Title = "Get price history"


Response = MsgBox(Msg, Style, Title)


If Response = vbOK Then


MyString = "Yes"


Else


End


End If


d = (Month(Date) - 1) 'd=Month(Date)-1 (Start Date)


e = Day(Date) 'e=day(Date) (Start Date)


f = Year(Date) 'f=Year(Date) (Start Date)


a = (Month(Date) - 1) 'a=6(month) (End Date)


b = Day(Date) 'b=28(day) (End Date)


c = (Year(Date) - 14) 'c=1998(year) (End Date)


Symbol = ActiveCell.Value


'http://ichart.finance.yahoo.com/table.csv?s=EPD&a=6&b=28&c=1998&d=2&e=18&f=2014&g=d&ignore=.csv


ThisURL = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol & _


"&a=" & (Month(Date) - 1) & "b=" & Day(Date) & "&c=" & (Year(Date) - 14) & _


"&d=" & (Month(Date) - 1) & "e=" & Day(Date) & "f=" & (Year(Date)) & "g==d&ignore=.csv"



Set QT = ActiveSheet.QueryTables.Add( _


Connection:="URL;" & ThisURL, _


Destination:=Range("A2"))


With QT


.Refresh BackgroundQuery:=False


End With


For Each QT In Worksheets(ActiveSheet.Name).QueryTables


QT.Delete


Next QT


Selection.CurrentRegion.Select


Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _


Comma:=True, FieldInfo _


:=Array(Array(1, 3), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _


Array(7, 1)), TrailingMinusNumbers:=True


Columns("A:G").EntireColumn.EntireColumn.AutoFit


Range("A1").Select


Sheets(ActiveSheet.Name).Name = Symbol & "-Y"


End Sub


photo

 Josias K., Financial Markets Analyst

 Wednesday, August 20, 2014



Thanks everyone.

To David Lanman, your excel code is working great, but to import datas for US stocks. i'll try to extract useful macros from yours.

But now i have somes ideas like pdfbox, pdf library, thanks to all of you.

I'm not a java user, so i'm still trying to find an effective way to do it myself. I think i'll have to use excel spreadsheets and macros for now, the time to find or to get used to java.


photo

 private private,

 Wednesday, August 20, 2014



Glad it works. Thanks, David


photo

 Salah Elmorry, Founding Partner at Systemathics

 Thursday, August 21, 2014



Hi Josias,


Maybe you have to try this : http://www.quandl.com


They are providing histo data for free; different formats are available.


Kind regards,


Salah

Please login or register to post comments.

TRADING FUTURES AND OPTIONS INVOLVES SUBSTANTIAL RISK OF LOSS AND IS NOT SUITABLE FOR ALL INVESTORS
Terms Of UsePrivacy StatementCopyright 2018 Algorithmic Traders Association