Re: [Tutor] Questions

[George Fischhof]

Diana Katz  ezt írta (időpont: 2019. ápr. 7., V,
10:01):

> 1) Can you use python from excel? Or just export to excel?
> 2) I am trying to see if there's a way using python to automate all of this
> work that I need to do. I have to collect quarterly segment data for
> hundreds of public companies and go back at least 12-16 quarters. We use an
> aggregator like factset and they actually don't have this option available
> in an automated way. So I'm trying to see if there's a way to build this.
> Basically, I get my data from sec.gov and they have interactive data -
> they
> even have the data in excel (though it's a messy file and hard to read). I
> attached some of the steps and the data that i'd want to see.
> Basically i'd want the excel to look like:
> old to new quarters - going back 12 to 16 quarters (more if possible but
> not if it will stop the project).
>  Columns: 3/31/2017, 6/30/2017, 9/30/17, 12/31/17, 3/313/2018...
> Rows:
> Sales for segment A
> Sales for Segment b
> Sales for SEgment C
> …(for as many segments as they have)
>
> Earnings for Segment A
> .Earnings for Segment B
>
> Depreciation for Segment A
> Depreciation for Segment B
> Depreciation for Segment C...
>
> I included where I get the data in the attached document.
>
> All the best,
>
> Diana Katz
> ___
> Tutor maillist  -  Tutor@python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor



Hi Diana,

you can write excel addins in Python with this program:
https://www.pyxll.com/index.html

and you can use excel from Python too.
The two ways:

- If you do not need the excel process itself (it means that you do not
want to recalculate for example, or do any calculation, then perhaps
openpyxl would be a great choice for you:
https://pypi.org/project/openpyxl/
there are some templates and other stuff as well:
https://pypi.org/search/?q=openpyxl

- the other way is, when you want to  do calculations, then xlwings is your
package:
https://pypi.org/project/xlwings/
it uses win32com API, there are some edge cases where it does not allow you
to access low level com API functions. I faced with this, and created an
excel helper over win32com API instead of using xlwings, but I do not think
you will need that. (If yes, then code of xlwings can help you


BR,
__george__
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] Interoperating with Excel, was Re: Questions

[Albert-Jan Roskam]

On 7 Apr 2019 14:07, Peter Otten <__pete...@web.de> wrote:

Diana Katz wrote:

> 1) Can you use python from excel? Or just export to excel?
> 2) I am trying to see if there's a way using python to automate all of
> this work that I need to do. I have to collect quarterly segment data for
> hundreds of public companies and go back at least 12-16 quarters. We use
> an aggregator like factset and they actually don't have this option
> available in an automated way. So I'm trying to see if there's a way to
> build this. Basically, I get my data from sec.gov and they have
> interactive data - they even have the data in excel (though it's a messy
> file and hard to read). I attached some of the steps and the data that i'd
> want to see. Basically i'd want the excel to look like:
> old to new quarters - going back 12 to 16 quarters (more if possible but
> not if it will stop the project).
>  Columns: 3/31/2017, 6/30/2017, 9/30/17, 12/31/17, 3/313/2018...
> Rows:
> Sales for segment A
> Sales for Segment b
> Sales for SEgment C
> …(for as many segments as they have)
>
> Earnings for Segment A
> .Earnings for Segment B
>
> Depreciation for Segment A
> Depreciation for Segment B
> Depreciation for Segment C...

These look like "pivot tables" which are well supported by Excel.
I expect that this is easy to automate with a little bit of Basic.

Of course you can build these tables with a Python script if you feel more
comfortable in Python. Then either write them into csv files ("comma
separated value", supported by the standard library)

https://docs.python.org/3/library/csv.html

which can be read by Excel -- or use a dedicated library. Google came up
with

https://xlsxwriter.readthedocs.io/


===>> There's xlrd for xls files, openpyxl for xlsx files. And pandas can also 
read excel, and you can use groupby, pivottable etc. there:
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html.
 With the win32com library you can also automate Excel (Windows only).

Excel is evil, especially if the files are made by humans and come from 
different sources.




___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] Questions

[William Ray Wing via Tutor]

Diana, I’m answering you via the Tutor list - please, the accepted protocol is 
to send all questions and answers to the list so answers can be seen by (and 
possibly help) others.

Having said that, I should have paid more attention to your original question, 
which is really going to require answers that are beyond the typical Tutor 
question level, so I’m also forwarding to the main Python list where you should 
be able to get pointers.

But let me ask, how much programming do you know? Python is a full-blown 
programming language, like Java or C.  Have you written programs before that, 
for example can accept a file name from a user, open that file, and read its 
contents?  If yes, then I apologize, and would point you at:


https://medium.freecodecamp.org/how-to-scrape-websites-with-python-and-beautifulsoup-5946935d93fe
 


https://towardsdatascience.com/how-to-web-scrape-with-python-in-4-minutes-bc49186a8460

https://realpython.com/python-web-scraping-practical-introduction/
or
https://docs.python-guide.org/scenarios/scrape/

The next steps would probably involve loading that scraped data into Pandas:


https://pandas.pydata.org/pandas-docs/stable/getting_started/tutorials.html


https://data36.com/pandas-tutorial-1-basics-reading-data-files-dataframes-data-selection/

https://www.tutorialspoint.com/python_pandas

On the other hand, if your answer to my question is: “no” - then you should 
take a look at any of the really vast
collection of web sites devoted to Python learning.  Note that Python was 
originally designed to be a language that would be easy for beginners to learn. 
 It still is - I’d claim it is about the easiest -

>>> print( "Hello world!" ) 
Hello world!

Those lines were lifted from Alan Gauld’s learn to program web site.

Let us know how we can help.

Bill


> On Apr 8, 2019, at 5:40 PM, Diana Katz  wrote:
> 
> Yes - data would need to be scraped from sec.gov  website. 
> I want to be able to pull up segment data from 10-Q filings of individual 
> companies by putting in a ticker (preferably in excel, but an be done 
> elsewhere).  Trying to figure out how to even start setting this up. 
> 
> Thank you!
> 
> On Sun, Apr 7, 2019 at 8:57 PM William Ray Wing  > wrote:
> 
> 
> > On Apr 5, 2019, at 8:01 PM, Diana Katz  > > wrote:
> > 
> > 1) Can you use python from excel? Or just export to excel?
> 
> Simple answer: no.  Python can read and write excel files through libraries:
> 
>  https://www.datacamp.com/community/tutorials/python-excel-tutorial 
>  
> 
> > 2) I am trying to see if there's a way using python to automate all of this
> > work that I need to do. I have to collect quarterly segment data for
> > hundreds of public companies and go back at least 12-16 quarters. We use an
> > aggregator like factset and they actually don't have this option available
> > in an automated way. So I'm trying to see if there's a way to build this.
> 
> We really need more information to be of any help.  Is the starting data 
> coming from a web site?
> Python scripts can interact with web sites, “scrape” data from them or read 
> data from files downloaded in response to the script's interaction with the 
> site. The python library Pandas (named by its originator in the financial 
> field where such data is referred to as “panel” data) is optimized for 
> manipulating spreadsheet-like tables of data (it includes a pivot operation).
> 
> > Basically, I get my data from sec.gov  and they have 
> > interactive data - they
> > even have the data in excel (though it's a messy file and hard to read). I
> > attached some of the steps and the data that i'd want to see.
> > Basically i'd want the excel to look like:
> > old to new quarters - going back 12 to 16 quarters (more if possible but
> > not if it will stop the project).
> > Columns: 3/31/2017, 6/30/2017, 9/30/17, 12/31/17, 3/313/2018...
> > Rows:
> > Sales for segment A
> > Sales for Segment b
> > Sales for SEgment C
> > …(for as many segments as they have)
> > 
> > Earnings for Segment A
> > .Earnings for Segment B
> > 
> > Depreciation for Segment A
> > Depreciation for Segment B
> > Depreciation for Segment C...
> > 
> > I included where I get the data in the attached document.
> 
> Since attachments can contain unknown contents, this list drops them.
> 
> Bill
> 
> > 
> > All the best,
> > 
> > Diana Katz
> > ___
> > Tutor maillist  -  Tutor@python.org 
> > To unsubscribe or change subscription options:
> > https://mail.python.org/mailman/listinfo/tutor 
> > 
> 

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/ma