My entire code is below. However, read my whole post since much of it works well enough.
from selenium import webdriver
import os
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import time
from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.keys import Keys
import openpyxl
import pandas as pd
import numpy as np
trade_date_lim = "5/1/2021"
chrome_driver = os.path.abspath('C:/Users/ross/Desktop/chromedriver.exe')
browser = webdriver.Chrome(chrome_driver)
#path is workbook I want to read from, eventually write to
path = r"C:\Users\ross\Desktop\reit.xlsx"
workbook = openpyxl.load_workbook(path)
sheet = workbook["Tickers"]
max_rows = sheet.max_row
ticker_array = []
for row in range(2, max_rows + 1):
ticker = sheet.cell(row=row, column=1)
ticker_array.append(ticker.value)
wait = WebDriverWait(browser, 10)
symbol_array = []
for stock in ticker_array:
browser.get('https://finra-markets.morningstar.com/BondCenter/Default.jsp')
#using clicks and send_keys, gets the bond page for a desired stock
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR,
'#TabContainer > div > div.rtq-tab-wrap > div.rtq-tab-menus-wrap > ul > li:nth-child(3) > a > span'))).click()
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, '#firscreener-cusip'))).send_keys(stock)
try:
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.qs-no-info")))
except TimeoutException:
pass
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR,
"#ms-finra-advanced-search-form > div.ms-finra-advanced-search-btn > input:nth-child(2)"))).click()
try:
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-agreement > input"))).click()
except TimeoutException:
pass
#clicks to sort by earliest date, clicks again to sort by latest maturity
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-grid-hd > div > div:nth-child(7) > div"))).click()
time.sleep(5)
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-grid-hd > div > div:nth-child(7) > div"))).click()
time.sleep(5)
#gathers all bond offerings on first page
whole_chart = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll"))).text
#gets number of bonds listed on page so we can iterate through them. Some pages have differing number of bonds listed. Most on page is 20
parent = browser.find_element_by_xpath('//*[@id="ms-finra-search-results"]/div/div[3]/div[1]/div[1]/div[2]/div[2]/div')
count_divs = len(parent.find_elements_by_xpath("./div"))
bnd_off_cnt = 1
row_num = 0
while row_num < count_divs and bnd_off_cnt < 3:
#gets values that I'm looking for
symbol = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll > div > div:nth-child(" + str(row_num + 1) + ") > div:nth-child(3)"))).text
maturity = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll > div > div:nth-child(" + str(row_num + 1) + ") > div:nth-child(7)"))).text
moody_rating = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll > div > div:nth-child(" + str(row_num + 1) + ") > div:nth-child(8)"))).text
sandp_rating = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll > div > div:nth-child(" + str(row_num + 1) + ") > div:nth-child(9)"))).text
stated_bond_yield = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll > div > div:nth-child(" + str(row_num + 1) + ") > div:nth-child(11)"))).text
#looks to see if all values are non-empty and if moody rating and sandp rating are not equal to 'WR' and 'NR'
if symbol.strip() and maturity.strip() and moody_rating.strip() and sandp_rating.strip() and stated_bond_yield.strip() and moody_rating != "WR" and sandp_rating != "NR":
#bond detail page below
element = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-finra-search-results > div > div.qs-resultData > div.qs-resultData-body > div.rtq-grid.rtq-grid-auto-h > div.rtq-scrollpanel > div.rtq-grid-scroll > div > div:nth-child(" + str(row_num + 1) + ") > div:nth-child(2) > div > a")))
element_link = element.get_attribute('href') #gets the link
#opens window, switches to it and opens the bond detail page
browser.execute_script("window.open('');")
time.sleep(3)
browser.switch_to.window(browser.window_handles[1])
browser.get(element_link)
#switch to iframe on second page and clicks it
wait.until(EC.frame_to_be_available_and_switch_to_it((By.ID, "ms-bond-detail-iframe")))
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR,"#tradeHistory_link"))).click()
#switches to third page
browser.switch_to.window(browser.window_handles[-1])
#sleeps for 3 seconds so we know for sure that we are working on right page
time.sleep(3)
# get length of table on trades page and iterate through them trying to find the most recent "Trade" status
bond_trades = wait.until(EC.presence_of_all_elements_located((By.CSS_SELECTOR, "#ms-glossary > div > table > tbody > tr")))
count = len(bond_trades)
for trade in range(count):
bond_trade_status = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-glossary > div > table > tbody > tr:nth-child(" + str(trade + 1) + ") > td:nth-child(4) > div"))).text
if bond_trade_status == "Trade":
bond_last_traded = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-glossary > div > table > tbody > tr:nth-child(" + str(trade + 1) + ") > td:nth-child(1) > div"))).text
if bond_last_traded > trade_date_lim:
#prior bond yields occasionally don't match the yield that it was last traded at
bond_yield = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#ms-glossary > div > table > tbody > tr:nth-child(" + str(trade + 1) + ") > td:nth-child(7) > div"))).text
symbol_array.append(symbol)
bnd_off_cnt += 1
break
else:
continue
#test for if we are within X amount of time from today
#continue if we are more than that amount of time
#exit if we are within time frame and get 'Yield'
else:
continue
browser.switch_to.window(browser.window_handles[2])
browser.close()
browser.switch_to.window(browser.window_handles[1])
browser.close()
browser.switch_to.window(browser.window_handles[0])
row_num += 1
np_symbol_arr = np.array(symbol_array)
np_symbol_arr.resize((3,1))
print(np_symbol_arr.shape)
print(np_symbol_arr)
df = pd.DataFrame([np_symbol_arr])
path = r"C:\Users\ross\Desktop\reit.xlsx"
book = openpyxl.load_workbook(path)
writer = pd.ExcelWriter(path, engine="openpyxl")
writer.book = book
df.to_excel(writer, sheet_name="Healthcare")
writer.save()
I am getting an error on this line near the very bottom of my code: df = pd.DataFrame([np_symbol_arr]). The lines previous I print out the shape of the numpy array and the values in the numpy array. The shape is (3, 1) and the values are as follows:
[['NHI5113928']
['MPW5083277']
['MPW4860582']]
So it looks like I am passing a 2-d array (or so I think). What is going on here? Why does it think I'm passing a 1, 3, 1 array?
Error type: ValueError: Must pass 2-d input. shape=(1, 3, 1)
Any and all help appreciated!
Ross
Is it because you have embedded your array inside another Python list. That becomes the 1st dimension. Change your call to
df = pd.DataFrame(np_symbol_arr)
without the extra brackets.