Can't store output from Colab into Excel-CodePudding

I wrote the code below but was unable to save it into Excel.

!pip install selenium
!apt-get update # to update ubuntu to correctly run apt install
!apt install chromium-chromedriver
!cp /usr/lib/chromium-browser/chromedriver /usr/bin
import sys
sys.path.insert(0,'/usr/lib/chromium-browser/chromedriver')
from selenium import webdriver
chrome_options = webdriver.ChromeOptions()
chrome_options.add_argument('--headless')
chrome_options.add_argument('--no-sandbox')
chrome_options.add_argument('--disable-dev-shm-usage')
wd = webdriver.Chrome('chromedriver',chrome_options=chrome_options)

lists = ["FBRX", "GNLN", "TISI"]

for list in lists:
  url = "https://finance.yahoo.com/quote/{list}?p={list}"
  wd.get(url.format(list=list))
  EPS = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[2]/table/tbody/tr[4]/td[2]/span').text
  AV = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[1]/table/tbody/tr[8]/td[2]/span').text
  OYT = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[2]/table/tbody/tr[8]/td[2]/span').text
  print(list,EPS,AV,OYT)

It will output the below table. But after that, I can't make the below into Excel. I had tried many methods but still fail. How can I fix this?

FBRX -1.6060 2,031,998 3.25
GNLN -1.0530 827,585 5.40
TISI -2.4640 545,536 10.00

CodePudding user response：

This should do the job, replace intead of the second part of the code you posted:

lists = ["FBRX", "GNLN", "TISI"]

import pandas as pd # Import Pandas
df = pd.DataFrame(columns=range(len(lists)), index=lists) # Create empty DataFrame

for i, list in enumerate(lists):
  url = "https://finance.yahoo.com/quote/{list}?p={list}"
  wd.get(url.format(list=list))
  EPS = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[2]/table/tbody/tr[4]/td[2]/span').text
  AV = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[1]/table/tbody/tr[8]/td[2]/span').text
  OYT = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[2]/table/tbody/tr[8]/td[2]/span').text
  print(list,EPS,AV,OYT)
  df[i][0]=EPS # Fill line i-th, column 0
  df[i][1]=AV # Fill line i-th, column 1
  df[i][2]=OYT # Fill line i-th, column 2

df.to_excel("output.xlsx") # Save to excel file

Basically, you create a DataFrame, which is like an empty table, by using Pandas ("a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language"). Then, for every loop, you fill a line of the DataFrame. Finally, you invoke the to_excel() method to store the file as output.xlsx. You will find it in the content folder of Google Colab.

CodePudding user response：

Build a list of results in the for loop, use Pandas to make a data frame, and from this create the spreadsheet.

lists = ["FBRX", "GNLN", "TISI"]
result=[]  # empty list to start
for list in lists:
  url = f"https://finance.yahoo.com/quote/{list}?p={list}" # use an f string to format
  wd.get(url.format(list=list))
  EPS = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[2]/table/tbody/tr[4]/td[2]/span').text
  AV = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[1]/table/tbody/tr[8]/td[2]/span').text
  OYT = wd.find_element_by_xpath('//*[@id="quote-summary"]/div[2]/table/tbody/tr[8]/td[2]/span').text
  print(list,EPS,AV,OYT)
  result.append([list,EPS,AV,OYT])   # add the row to the results

result

#[['FBRX', '-1.6060', '2,031,998', '3.25'],
# ['GNLN', '-1.0530', '827,585', '5.40'],
# ['TISI', '-2.4640', '545,536', '10.00']]

import pandas as pd
df = pd.DataFrame(result, columns=['List','EPS','AV','OYT'])
df.to_excel('result.xlsx')

Note that I had to make the url generation use an f string to get the url correct.